Multiple options- answer in single cel
Hi Guys,
What a very helpful bunch of guys. I would appreciate help on this one Cells A1,A2 and C2 contain the numbers 5, 10 and 15 respectively. These numbers relate to the price of something which is conditional on the number in B1. e.g if B1 is 249 or less A1 is 5, if B1 is between 249 and 499 then A2 is 10, if 500 or higher than 500 C2 is 15 Cell B1 can contains any number -- say, for example 250 Cell C1 should display the answer to cell B1multiplied by either 5, 10 or 15, depending on the number in B1. Help please!! Cell C1 is the answer to the formu Peterest |
Multiple options- answer in single cel
A1: =IF(B1<=249,5,0)
A2: =IF(AND(B1249,B1<500),10,0) C2: =IF(B1=500,15,0) (should this be in A3 ?) C1: =B1*(A1+A2+C2) If you do not like the zeros, use A1: =IF(B1<=249,5,"") ---- that two double quotes in places of the zero Do same with other IF formulas Replace C1 formula by =B1*SUM((A1:A2,C2)) because SUM can handle a formula that returns a 'null-string' And if you want to dispense with the A1, A2, C2 formulas and use only C1: =B1*((B1<250)*5+(B1249)*(B1<500)*10+(B1=500)*15) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Peterest" wrote in message ... Hi Guys, What a very helpful bunch of guys. I would appreciate help on this one Cells A1,A2 and C2 contain the numbers 5, 10 and 15 respectively. These numbers relate to the price of something which is conditional on the number in B1. e.g if B1 is 249 or less A1 is 5, if B1 is between 249 and 499 then A2 is 10, if 500 or higher than 500 C2 is 15 Cell B1 can contains any number -- say, for example 250 Cell C1 should display the answer to cell B1multiplied by either 5, 10 or 15, depending on the number in B1. Help please!! Cell C1 is the answer to the formu Peterest |
Multiple options- answer in single cel
Hi,
Try =IF(B1<250,(B1*A1),IF(B1<500,(B1*A2),IF(B1=500,(B 1*C2)))) "Peterest" wrote: Hi Guys, What a very helpful bunch of guys. I would appreciate help on this one Cells A1,A2 and C2 contain the numbers 5, 10 and 15 respectively. These numbers relate to the price of something which is conditional on the number in B1. e.g if B1 is 249 or less A1 is 5, if B1 is between 249 and 499 then A2 is 10, if 500 or higher than 500 C2 is 15 Cell B1 can contains any number -- say, for example 250 Cell C1 should display the answer to cell B1multiplied by either 5, 10 or 15, depending on the number in B1. Help please!! Cell C1 is the answer to the formu Peterest |
All times are GMT +1. The time now is 06:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com