Excel formula help needed

  Toni B 00:36 12 Mar 07
Locked

Good morning,
Can anybody tell me the errors in my excel formula please.

If value in c5 is less than 4.99 then value is 0.03,
If value in c5 is more than 5.00 but less than 9.99 value is 0.05,
If value in c5 is more than 10.00 but less than 49.99 value is 0.07,
If value in c5 is more than 50.00 but less than 499.99 value is 0.09.


=IF(C5 =4.99,C5=0.03),IF(C5 =9.99>5,C5=0.05),IF(C5 =49.99>10,C5=0.07),IF(C5 =499.99>50,C5=0.09)
Thanks

Toni

  VoG II 10:55 12 Mar 07

You can't assign a value to C5 based upon the value in C5. You also would need to nest the IFs as in

=IF(C5<4.49,0.03,IF(C5<9.99......

but this is neater

=LOOKUP(C5,{0,0.03;5.01,0.05;10.01,0.07;50.01,0.09})

entered in a cell other than C5.

  VoG II 11:04 12 Mar 07

Actually your cut-off points don't seem terribly consistent. What should happen if the value in C5 is exactly 50?

More consistent

=LOOKUP(C5,{0,0.03;5,0.05;10,0.07;50,0.09})

or

=IF(C5<5,0.03,IF(C5<10,0.05,IF(C5<50,0.07,0.09)))

  Toni B 12:42 12 Mar 07

The last formulas was ideal VoG.
Thank you for taking time to help
Regards Toni

  Toni B 12:49 12 Mar 07

Thanks

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…