IF function

  • Thread starter Thread starter OrcaFire
  • Start date Start date
O

OrcaFire

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.
 
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.
 
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
 
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?
Click to expand...

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.
 
=IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}))
the other formula returns a "FALSE"
Click to expand...

Hmmm...

That's not possible if you used the above formula!

Anyhow, I like the CEILING formula better.

Thanks for the feedback!
 
Back
Top