2

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. Below is my logic. Everything is calculating correctly up until the third if statement. I can't figure out why

=IF(AV2<=5,AV2*4,
 IF(OR(AV2>=5.01,AV2<=11),AV2*3.2,
 IF(OR(AV2>=11.01,AV2<=15.5),AV2*2.8,
 IF(OR(AV2>=15.51,AV2<=20.5),AV2*2.7,
 IF(AV2>=20.51,AV2*2.3)))))
0

you need AND instead of OR

=IF(AV2<=5                   ,AV2*4,
 IF(AND(AV2>=5.01, AV2<=11)  ,AV2*3.2,
 IF(AND(AV2>=11.01,AV2<=15.5),AV2*2.8,
 IF(AND(AV2>=15.51,AV2<=20.5),AV2*2.7,
 IF(AV2>=20.51               ,AV2*2.3,)))))

  • I originally started with AND instead of OR but it just returned a result of FALSE. I need to have an actual number as a result. I get the FALSE result because all of the conditions were not met. I switched to OR because I only one of the conditions to be met. – Megan C Jan 2 at 20:40
  • @MeganC in that case add one more , to get rid of FALSE if thats what you want – MARK MY ANSWER Jan 2 at 20:44
  • thank you! that did the trick :) – Megan C Jan 2 at 20:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.