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.

 

[steve the fish]

Whats the 1.00575 for?

 

[jasonb75]

Using the legend as a reference instead of hard coding the formula will make life much easier.

The List.xlsx
	A	B	C	D	E
1				Legend:
2	1,500,000.00	0.04	37,200,000.00	Price	Shares
3				0.00	1,000,000
4				0.01	100,000
5				0.05	10,000
6				0.25	10,000
7				0.50	1,000
8				5.00	100
9				10.00	100
10				20.00	100
11				50.00	10
12				100.00	10
13				200.00	10
14				500.00	10
15				1,000.00	5
16				2,000.00	5
17				5,000.00	5
Sheet15

Cell Formulas
Range	Formula
C2	C2	=FLOOR(A2/(B2*1.00575),LOOKUP(B2,$D$2:$D$17,$E$2:$E$17))

To make your formula work, you need to use AND instead of OR for the criteria.

 

[shaowu459]

=ROUNDDOWN(1234567,-6)=1,000,000, so how about change -6 in "ROUNDDOWN(A2/(B2*1.00575),-6)" to -5?

 

[Doth83]

Whats the 1.00575 for?

That is the transaction charges. I want to get the exact number of shares with the transaction charges factored in

 

[Doth83]

Thank yo

Using the legend as a reference instead of hard coding the formula will make life much easier.

The List.xlsx
	A	B	C	D	E
1				Legend:
2	1,500,000.00	0.04	37,200,000.00	Price	Shares
3				0.00	1,000,000
4				0.01	100,000
5				0.05	10,000
6				0.25	10,000
7				0.50	1,000
8				5.00	100
9				10.00	100
10				20.00	100
11				50.00	10
12				100.00	10
13				200.00	10
14				500.00	10
15				1,000.00	5
16				2,000.00	5
17				5,000.00	5
Sheet15

Cell Formulas
Range	Formula
C2	C2	=FLOOR(A2/(B2*1.00575),LOOKUP(B2,$D$2:$D$17,$E$2:$E$17))

To make your formula work, you need to use AND instead of OR for the criteria.

Thank you very much for this. I used your code and I also edited my code to make it AND. I just looked up on the internet on how to do this formula. I had no prior knowledge in making formulae using excel. I just thought OR was a good logic to use, but I was wrong. AND was the correct function to use.

Is using your code make the processing of results faster or just the same? I guess your code makes it easier to type instead of mine, right? Also, it's more convenient to use if there's a table as reference. Any other benefits?

 

[Doth83]

=ROUNDDOWN(1234567,-6)=1,000,000, so how about change -6 in "ROUNDDOWN(A2/(B2*1.00575),-6)" to -5?

Hi, no... it has to be -6 based on the legend. Stock prices from 0.0001-0.0099 are bought in increments of 1,000,000 shares. Hence -6, not -5.

 

[jasonb75]

Any other benefits?

Much easier to error check and to update if the criteria change in future.

How efficient the formula processes depends on a number of factors, not just the way that the formula is written. The table formula wouldn't be any less efficient than your method.

 

[Doth83]

Much easier to error check and to update if the criteria change in future.

How efficient the formula processes depends on a number of factors, not just the way that the formula is written. The table formula wouldn't be any less efficient than your method.

Right, thank you very much. It helped. I think I will use your formula instead

 

[joeu2004]

I had no prior knowledge in making formulae using excel. I just thought OR was a good logic to use, but I was wrong. AND was the correct function to use.

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.