Many Condition with IF Function

[Aumkar]

Hi All

I want to create a formula which calculates the cell value based on another cells value.

For Eg :-

=IF(F2<=5000, (5000*0.01%), "", IF(F2>5000 BUT <=10000, (10000*.01%), "" ))

I want to calculate the value based on the F2 Cell. So, suppose if F2 Cell is less than 5000, then the answer should be 5000*0.01%.
If it is greater than 5000 but less than 10000, then 10000*0.01%.

The same thing goes on with multiples of 5000. [IF greater than 10000 but less than 15000, then 15000*0.01%, IF greater than 15000 but less than 20000, then 20000*0.01% .........]

If the figure in the cell F2 comes out as 50,000 or 1,00,000 then i will have to write many IF conditions
Is there any way by which i can calculate without writing so many IF's ?

[DonkeyOte]

Your operators are confusing - your formula says <= 10000 but your narrative says < 10000 ... which is it ?

If we assume the narrative is correct then

=(FLOOR(F2,5000)+5000)*0.1%

[Aumkar]

Your operators are confusing - your formula says <= 10000 but your narrative says < 10000 ... which is it ?

If we assume the narrative is correct then

=(FLOOR(F2,5000)+5000)*0.1%

Thanks for the reply. I forgot to add 'equal to' sign while narrating.
So, what modifications to the above formula shall i make so that it takes into account the <= condition.

[arthurbr]

Maybe =CEILING(F2,5000)*.01%

[Aumkar]

Maybe =CEILING(F2,5000)*.01%

Thanks, this works

[DonkeyOte]

Given the <= operator the CEILING formula you've been given does exactly what you ask.

The formula takes the value in F2 and rounds up to next nearest multiple of 5000 - and then multiplies that final value by 0.01%

Please test before disregarding based on interpretation.

[Aumkar]

Sorry! I tested, but while writing the message here wrote something else.

It was my mistake that i was not able to state the problem correctly in the last post. Pardon me.
I was about to ask, what modification to the formula shall be made so that it takes into account the < condition and not <=.

[DonkeyOte]

see post # 2