Author Topic: AND function Excel  (Read 3189 times)

Tags:
  • All members
  • Posts: 490
  • Gender: Male
AND function Excel
« on: 02 Mar 2013, 05:42 »
I am trying to write a formula for the custom cells that uses the Excel command AND
All I get is FALSE returned.
The formula works in Excel. It does include an MFP formula name

IF(AND(P(sel_index*3+1)>0,P(sel_index*3+1)<8),1,0) always returns FALSE?

The Help files say any formula the works in Excel can be used in custom formulas. This is false.

Oxa?
Improvise Adapt Overcome

  • All members
  • Posts: 490
  • Gender: Male
Re: AND function Excel
« Reply #1 on: 02 Mar 2013, 05:58 »
I read...

AND(expression1, expression2, ...). Returns true if all the expressions inside the brackets are true, and false otherwise.

But I see....
SUM(B(sel_index*3+1):D(sel_index*3+1))

noting the colon : instead of a comma ???
Improvise Adapt Overcome

  • Élite
  • Posts: 3698
  • Gender: Male
*
Re: AND function Excel
« Reply #2 on: 02 Mar 2013, 10:52 »
hi
that is because you have used AND
BUT you have not put the ) to close the AND condition, it should be
IF(AND(P(sel_index*3+1)>0,P(sel_index*3+1)<  ;D),1,0)
NOTICE the ) after the smile

mcbee
Please read the following  disclaimer with regards to the information you may request and obtain on our forum. This specifically concerns trigger files and various instructions as to how to implement a strategy.

  • Administrator
  • Posts: 8821
  • Gender: Female
*
Re: AND function Excel
« Reply #3 on: 02 Mar 2013, 12:17 »
Just to let you know that I modified your messages to remove the smileys. There is an option in the "Additional options" link which says "Don't use smileys". It totally makes sense when posting formulae.

After removing the smileys it becomes obvious that londolozi did close all the brackets.

However, londolozi is mixing up the MF Pro and Excel syntax incorrectly here. To obtain the values of the cells in column P, you must refer to them with the corresponding trigger variable which ought to have the following syntax:

cell_[sheet_name]_[cell_address]

So your original notation transforms into:

IF(AND(cell_querymarket_id_P(sel_index*3+1)>0,cell_querymarket_id_P(sel_index*3+1)<8),1,0)

You indeed can include any Excel formula in a custom cell, but in that case you must put an equation sign in front of it:

=SUM(B(sel_index*3+1):D(sel_index*3+1))

So how come this expression is parsed correctly and your formula is not?
The thing is, before transferring the body of the custom cell formula to Excel, MF Pro will parse as much of the expression as it can. It will substitute every operator, function, variable etc. it can with the corresponding value.

So take this formula:

=SUM(B(sel_index*3+1):D(sel_index*3+1))

The word SUM is unknown to MF Pro, yet it does replace the variable sel_index with a number, and it does perform the arithmetic calculation. So the bit that goes on to the spreadsheet will look like this:

=SUM(B4:D4)

The rest is done by Excel, i.e. Excel calculates the sum of this cell range.

Now take a look at this:

IF(AND(P(sel_index*3+1)>0,P(sel_index*3+1)<8),1,0)

Since IF and AND can be parsed by MF Pro, their syntax will be expected from your expression. After replacing the variables and performing the arithmetics this is what will remain:

IF(AND(P4>0,P4<8),1,0)

The program will now do the comparison: P4 is between 0 and 8? No, it is false, because to MF Pro, P4 is a combination of a letter and a digit, not a cell. Therefore the whole expression will always return 0. And it is 0 that will be passed on to Excel.

Putting the equation sign in front of IF will not help in this case, it will only result in "=0" being passed to the spreadsheet, again, because IF and AND will be parsed by the program, not by Excel.

Gosh, this makes such a long post, but I hope I've managed to clear it up a bit.
Always try your triggers in Test Mode before switching to real money!

Follow us on Twitter.

Join our WhatsApp chat!

Присоединяйтесь к официальному Telegram-каналу!

  • All members
  • Posts: 490
  • Gender: Male
Re: AND function Excel
« Reply #4 on: 02 Mar 2013, 23:11 »
Well explained Oxa. I get it now.
Thanks Mcbee always generous with your time.
Cheers

PS (smileyss off with formulas)  ;)
Improvise Adapt Overcome

 

Please note, BetFair is seems to be currently OFFLINE