conditional formula
How can i use a conditional formula to return one of 3 values ie. if A1 is
less than x return 10 if it is between x and y return 20 and if it is greater than y return 30 |
=(a1<x)*10 + (a1=x)*(a1<=y)*20 + (a1y)*30
-- Gary's Student "Matt" wrote: How can i use a conditional formula to return one of 3 values ie. if A1 is less than x return 10 if it is between x and y return 20 and if it is greater than y return 30 |
This does not seem to work, i am probably just doing somthing stupid. what
should i look at on the help to work through it (thanks for the help though) "Gary's Student" wrote: =(a1<x)*10 + (a1=x)*(a1<=y)*20 + (a1y)*30 -- Gary's Student "Matt" wrote: How can i use a conditional formula to return one of 3 values ie. if A1 is less than x return 10 if it is between x and y return 20 and if it is greater than y return 30 |
It's hard to make a suggestion unless you say what "does not seem to
work" means... Do you get an error? An incorrect value? A crash? What is your exact formula after you substitute your "x" and "y" variables for numbers or cell references? In article , "Matt" wrote: This does not seem to work, i am probably just doing somthing stupid. what should i look at on the help to work through it (thanks for the help though) |
Ok I have an order sheet for clothing, if total order quantities reach
certain levels i get a bulk discount. there are 3 levels of discount and so i wish to return one of 3 values. A1 contains the total no of garments 1-2 are £20, 3-10 are £15 and 10+are £10 hence i wish a formula which says: if A1<=2 return a value of 20 if 3<=A1<=9 return a value of 15 and if A1=10 return a value of 10 the formula suggested returns #NAME? if there is a help function associated with this then i am fine to work through it myself but i can't find one, largely because i don't know what to search under thanks again "JE McGimpsey" wrote: It's hard to make a suggestion unless you say what "does not seem to work" means... Do you get an error? An incorrect value? A crash? What is your exact formula after you substitute your "x" and "y" variables for numbers or cell references? In article , "Matt" wrote: This does not seem to work, i am probably just doing somthing stupid. what should i look at on the help to work through it (thanks for the help though) |
Again, what is your exact formula?
It sounds like you're using "x" and "y" without defining them... The Help topic I'd suggest looking at is "Correct a #NAME! Error" In article , "Matt" wrote: Ok I have an order sheet for clothing, if total order quantities reach certain levels i get a bulk discount. there are 3 levels of discount and so i wish to return one of 3 values. A1 contains the total no of garments 1-2 are £20, 3-10 are £15 and 10+are £10 hence i wish a formula which says: if A1<=2 return a value of 20 if 3<=A1<=9 return a value of 15 and if A1=10 return a value of 10 the formula suggested returns #NAME? if there is a help function associated with this then i am fine to work through it myself but i can't find one, largely because i don't know what to search under thanks again "JE McGimpsey" wrote: It's hard to make a suggestion unless you say what "does not seem to work" means... Do you get an error? An incorrect value? A crash? What is your exact formula after you substitute your "x" and "y" variables for numbers or cell references? |
All times are GMT +1. The time now is 02:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com