Need help on IF formula

[mifzal.mufthi]

I have seen people in this forum helping each other in solving their issues which is very good for people like us who are stuck in finding solutions. I also have request on IF formula. I am designing a price table based on USD VALUE.

I want the results to appear in column "C" based on the application which is shown from A12:A19. Example:-

1) If the USD VALUE IS LESS THAN OR EQUAL TO 5, THEN WE NEED TO APPLY 150% (5*150%)
2) If the USD VALUE IS BETWEEN 6-10, THEN WE NEED TO APPLY 120%

I tried formulating but didn't work. My results are appearing in Column E2:E9

Please help me.

[shanermuls]

If the USD value is say 70, your if formula asks 'is this value greater than 6' - which it is so the condition is satisfied and it returns the 120%*USD

You need to reorder your formula to say - if(>6, if(>10, etc )

[acerrhod]

try this
=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*90%,IF(A2>5,A2*120%,A2*150%)))))))+A2

[acerrhod]

try this
=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*90%,IF(A2>5,A2*120%,A2*150%)))))))+A2

[mifzal.mufthi]

try this
=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*90%,IF(A2>5,A2*120%,A2*150%)))))))+A2

Thanks. It works. Great support!!!

[mifzal.mufthi]

try this
=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*90%,IF(A2>5,A2*120%,A2*150%)))))))+A2

I forgot to mention 1 more thing. I wanted to add a condition below USD 1. Please let me know the formula for below

BELOW 1 - 350%
1-5 = 175%
6-10 = 100%
11-15 = 75%
16-25 = 60%
26-45 = 30%
46-55 = 25%
56-75 = 20%
76 & ABOVE = 15%

[acerrhod]

=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*90%,IF(A2>5,A2*120%,IF(A2>1,A2*150%,A2*350%))))))))+A2

Try the above

[acerrhod]

=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*90%,IF(A2>5,A2*120%,IF(A2>1,A2*150%,A2*350%))))))))+A2

Try the above

[Kevin UK]

Hi mifzal.mufthi

Or may be!
=A2+(A2*LOOKUP(A2,{0,1,6,11,16,26,46,56,76},{3.5,1.75,1,0.75,0.6,0.3,0.25,0.2,0.15}))

[mifzal.mufthi]

=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*90%,IF(A2>5,A2*120%,IF(A2>1,A2*150%,A2*350%))))))))+A2

Try the above

Thanks but it is incorrect. When I put USD 1, I should get USD 2.75 because the formula should be

1) If it is below USD 1 then the application should be 350%
2) If it is between USD 1-5, then the application should be 175%

Please refer the table

BELOW 1 - 350%
1-5 = 175%
6-10 = 100%
11-15 = 75%
16-25 = 60%
26-45 = 30%
46-55 = 25%
56-75 = 20%
76 & ABOVE = 15%

[mifzal.mufthi]

Hi mifzal.mufthi

Or may be!
=A2+(A2*LOOKUP(A2,{0,1,6,11,16,26,46,56,76},{3.5,1.75,1,0.75,0.6,0.3,0.25,0.2,0.15}))

This works perfectly Guru. Thank you again.

[acerrhod]

sorry, just change the percentage multiplier with the first formula i posted somthing like this

=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*75%,IF(A2>5,A2*100%,IF(A2>1,A2*175%,A2*350%))))))))+A2

[mifzal.mufthi]

sorry, just change the percentage multiplier with the first formula i posted somthing like this

=IF(A2>75,A2*15%,IF(A2>55,A2*20%,IF(A2>45,A2*25%,IF(A2>25,A2*30%,IF(A2>15,A2*60%,IF(A2>10,A2*75%,IF(A2>5,A2*100%,IF(A2>1,A2*175%,A2*350%))))))))+A2

Can I have the exact formula please?

[acerrhod]

=if(a2>75,a2*15%,if(a2>55,a2*20%,if(a2>45,a2*25%,if(a2>25,a2*30%,if(a2>15,a2*60%,if(a2>10,a2*75%,if(a2>5,a2*100%,if(a2>0,a2*175%,a2*350%))))))))+a2