IF function

[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.

 

[T. Valko]

Try this:

=IF(A5="","",A1*MATCH(A5,{0,11,21,31,41,51,61,71}))

 

[Reitanos]

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.

 

[T. Valko]

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

 

[OrcaFire]

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]

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.

 

[OrcaFire]

Thanks, the CEILING function worked but the other formula returns a "FALSE"

 

[T. Valko]

=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!