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)))))
| improve this question | |
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,)))))

| improve this answer | |
  • 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 '19 at 20:40
  • @MeganC in that case add one more , to get rid of FALSE if thats what you want – user0 Jan 2 '19 at 20:44
  • thank you! that did the trick :) – Megan C Jan 2 '19 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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