| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
If the cell I am referencing is between 1 and 10 I want my value to be CellA1
X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Try this:
=IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
If you need to go further, you can use the row() function so that your
formula doesn't have to keep growing. There's probably a more efficient way than I've drawn up, but it would look like this: =A1*(TRUNC(ROW(A1)/10,0)+1) Of course the row numbers will change when you copy this down the spreadsheet from A1. On Sep 6, 1:17*pm, "T. Valko" wrote: Try this: =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Shorter:
I need to continue this process up to "between 71 and 80" Assuming the referenced cell will never exceed 80: =CEILING(A5,10)/10*A1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
I have a maintenance worker for every 5 sites with a salary of $ 3,500 per
month. Additional sites come on stream every month so once I hit the 6th site in month 5 I will now need two maintenance workers and the salary line on my P&L will now be $ 7,000 instead of $3,500. Sorry but I didn't understand your initial formula. Thanks "T. Valko" wrote: Shorter: I need to continue this process up to "between 71 and 80" Assuming the referenced cell will never exceed 80: =CEILING(A5,10)/10*A1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
You posted:
If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Both formulas I suggested will do the above. =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) =CEILING(A5,10)/10*A1 A5 is the cell you're referencing. If A5 = 9 the result will be A1*1. If A5 = 20 the result will be A1*2. If A5 = 77 the result will be A1*8. That's how I interpret your post. -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... I have a maintenance worker for every 5 sites with a salary of $ 3,500 per month. Additional sites come on stream every month so once I hit the 6th site in month 5 I will now need two maintenance workers and the salary line on my P&L will now be $ 7,000 instead of $3,500. Sorry but I didn't understand your initial formula. Thanks "T. Valko" wrote: Shorter: I need to continue this process up to "between 71 and 80" Assuming the referenced cell will never exceed 80: =CEILING(A5,10)/10*A1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Thanks, the CEILING function worked but the other formula returns a "FALSE"
"T. Valko" wrote: You posted: If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Both formulas I suggested will do the above. =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) =CEILING(A5,10)/10*A1 A5 is the cell you're referencing. If A5 = 9 the result will be A1*1. If A5 = 20 the result will be A1*2. If A5 = 77 the result will be A1*8. That's how I interpret your post. -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... I have a maintenance worker for every 5 sites with a salary of $ 3,500 per month. Additional sites come on stream every month so once I hit the 6th site in month 5 I will now need two maintenance workers and the salary line on my P&L will now be $ 7,000 instead of $3,500. Sorry but I didn't understand your initial formula. Thanks "T. Valko" wrote: Shorter: I need to continue this process up to "between 71 and 80" Assuming the referenced cell will never exceed 80: =CEILING(A5,10)/10*A1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
=IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71} ))
the other formula returns a "FALSE" Hmmm... That's not possible if you used the above formula! Anyhow, I like the CEILING formula better. Thanks for the feedback! -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... Thanks, the CEILING function worked but the other formula returns a "FALSE" "T. Valko" wrote: You posted: If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Both formulas I suggested will do the above. =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) =CEILING(A5,10)/10*A1 A5 is the cell you're referencing. If A5 = 9 the result will be A1*1. If A5 = 20 the result will be A1*2. If A5 = 77 the result will be A1*8. That's how I interpret your post. -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... I have a maintenance worker for every 5 sites with a salary of $ 3,500 per month. Additional sites come on stream every month so once I hit the 6th site in month 5 I will now need two maintenance workers and the salary line on my P&L will now be $ 7,000 instead of $3,500. Sorry but I didn't understand your initial formula. Thanks "T. Valko" wrote: Shorter: I need to continue this process up to "between 71 and 80" Assuming the referenced cell will never exceed 80: =CEILING(A5,10)/10*A1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}) ) -- Biff Microsoft Excel MVP "OrcaFire" wrote in message ... If the cell I am referencing is between 1 and 10 I want my value to be CellA1 X1 If the cell I am referencing is between 11 and 20 I want my value to be CellA1 X2 I need to continue this process up to "between 71 and 80" How would I write this IF function? Thank you for your help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
