Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Analyzing multiple IF formulas for a single answer | Excel Discussion (Misc queries) | |||
Printing single pages from multiple worksheets in a single print job | Excel Discussion (Misc queries) | |||
multiple lists that return an answer | Excel Worksheet Functions | |||
Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
Get Vlookup to look at multiple Rows for the answer | Excel Worksheet Functions |