MS Excel Question: IF, AND, OR problem.

  MartinT-B 17:12 25 Jan 05
Locked

I am an Excel novice trying to set up a spreadsheet for the calculation of a variable commission rate on various fees.


Briefly, we charge a variable 10, 15 or 20% commission rate dependant on the job.



I would like to have a formula acting on Cell F24 that looks a value in Cell A24, If that Value is 1, then take then calculate (-D24*0.10) IE deduct 10% of the sum in cell D24. Further, if the Value of cell A24 is 2 then calculate (-D24*0.15) and if the value is 3 then calculate (-D24*0.20).


Now, I can get any one calculation to work correctly by entering it manually each time.


I have tried the logical IF function, but that only offers two choices (True or False) and I have tried the OR function, but I cannot get it to work at all.


I have tried setting the A24 Value as a name (IE TV), but than it asks me to define the name and I have no idea what definition I should set, or how!

Thank you for yor time.

  Rigga 17:16 25 Jan 05

You can Nest the IF functions.

=IF(A24=1,-D24*0.1,IF(A24=2, -D24*0.15, -D24*0.2))

HTH. R.

  MartinT-B 17:23 25 Jan 05

Many thanks!

Tested and works - it's easy when you know how isn't it!

  VoG II 17:23 25 Jan 05

But your formulas look awry to me (comparing with the description).

=IF(A24=1,D24*0.9,IF(A24=2, D24*0.85, -D24*0.8))

  MartinT-B 17:42 25 Jan 05

Hello VoG,

I need the formula to make a deduction of (say) 10% of the Fee and enter that in the cell.


IE Fee £100.00 (Cell D24) Commission -£10.00 (Cell F24) VAT on Commission @17.5% [=SUM(F24*0.175)] in (Cell G24) NI (variable deducted at source) in Cell H24 and Total =SUM(D24:H24) in cell I24.

I have tried and tested Rigga's nested suggestion and it works.

However, if the suggestion can be improved, I am happy to listen and learn :)

  VoG II 17:45 25 Jan 05

I misunderstood. Sorry.

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

iPhone 7 review: a range of small updates add up to an excellent phone

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

How New York’s Stylin’ Seniors became a golden social media campaign

23 Apple Watch tips & secret features: Master your Apple Watch, Apple Watch Series 1 or Apple…