Using Nested IFs w/ ORs, ROUNDDOWN, FLOOR function

[Doth83]

Hi,

I made an excel file to help me buy stocks. So if I put the amount worth of stocks I want to buy and the at a certain price, excel should compute how much shares I should buy rounding down. Now, in my stockmarket, there are brackets of prices that allow you to buy in certain increments of shares of a stock. For example, if I buy a stock priced at 7, I can buy in increments of 100 shares. So if I buy 10,000 worth of stocks at 7, I can buy 1,400 shares.

This is the formula that I use, but it's not rounding down properly and when I input a different amount and price, nothing shows up. I have factored in the transaction charges in this formula. Do you know what's wrong with my formula?

=IF(OR(B2>=0.0001,B2<=0.0099),ROUNDDOWN(A2/(B2*1.00575),-6),IF(OR(B2>=0.01,B2<=0.049),ROUNDDOWN(A2/(B2*1.00575),-5),IF(OR(B2>=0.05,B2<=0.495),ROUNDDOWN(A2/(B2*1.00575),-4),IF(OR(B2>=0.5,B2<=4.99),ROUNDDOWN(A2/(B2*1.00575),-3),IF(OR(B2>=5,B2<=49.95),ROUNDDOWN(A2/(B2*1.00575),-2),IF(OR(B2>=50,B2<=999.5),ROUNDDOWN(A2/(B2*1.00575),-1),IF(B2>=1000,FLOOR(A2/(B2*1.00575),5))))))))

Legend:
Price	Shares
0.0001-0.0099	1,000,000
0.010-0.049	100,000
0.05-0.249	10,000
0.25-0.495	10,000
0.50-4.99	1,000
5-9.99	100
10-19.98	100
20-49.95	100
50-99.95	10
100-199.90	10
200-499.80	10
500-999.50	10
1000-1,999	5
2,000-4,998	5
5,000-up	5

Kindly check the image for the excel file.

 

[Doth83]

As noted earlier, using LOOKUP is best.

But for educational purposes, even using AND is unnecessary and possibility unreliable.

You original formula would be better written as follows:

=IF(B2>=1000, FLOOR(A2/(B2*1.00575),5),
IF(B2>=50, ROUNDDOWN(A2/(B2*1.00575),-1),
IF(B2>5, ROUNDDOWN(A2/(B2*1.00575),-2),
IF(B2>=0.5, ROUNDDOWN(A2/(B2*1.00575),-3),
IF(B2>=0.05, ROUNDDOWN(A2/(B2*1.00575),-4),
IF(B2>=0.01, ROUNDDOWN(A2/(B2*1.00575),-5),
ROUNDDOWN(A2/(B2*1.00575),-6)))))))

The formula depends on the left-to-right evaluation of IF() expressions.

The problem with expressions of the form IF(AND(B2>=5,B2<=49.95),...,IF(AND(B2>=50,B2<=999.5) is: unless you are careful, there might be values of B2 that are between 49.95 and 50 that are not covered by any case.

Thank you very much. I won't doubt your modified version of my formula is better. How come the last line (-6), doesn't have an IF? You're right about some values being not included in my formula. Thank you for pointing that out. What makes your modified version more reliable asides from it covering the values that were not covered by my original formula? Also you said "The formula depends on the left-to-right evaluation of IF() expressions." --> how is this relevant?

 

[jasonb75]

The main reliability issue with using AND would be the smaller decimals that can fall through the gaps if you're not careful. There are other potential issues with it in different types of formulas that would not occur here (mainly arrays).

The last one doesn't need an IF by process of elimination, when all other possibilities have been exhausted the last option (-6) is all that is left so there is no need to check it first.

With left to right evaluation, you always need the criteria with the highest value on the left when using greater than, or the lowest when using less than. By process of elimination, to compare the right way and the wrong way

=IF(B2>50,1,IF(B2>5,2,3)) any value that is greater than 50 will return 1 from the first IF, if it is less than 50 then it moves to the second IF greater than 5 returns 2, this only leaves values less than or equal to 5, which return 3. We don't need to test if B2<=5 because everything else has already been eliminated.

=IF(B2>5,1,IF(B2>50,2,3)) by reversing the 2 IF's, any value greater than 5 will return 1 from the first IF, because any value greater than 50 is also greater than 5, the result of the first If will also apply to those values because the second IF is not evaluated.

Also, another way to write the formula with IF's would be

=IF(B2>=1000, FLOOR(A2/(B2*1.00575),5),
ROUNDDOWN(A2/(B2*1.00575),
IF(B2>=50, -1,
IF(B2>5,-2,
IF(B2>=0.5,-3,
IF(B2>=0.05,-4,
IF(B2>=0.01,-5,-6)))))))

There are often a number of ways to achieve the correct result, in most cases people will have different opinions as to which is the 'best' formula for the task.