Help with formulas in Excel?

Question

I run a small business and have been using ledger books to keep my records. But I'm thinking of just buying Microsoft Excel. Before I do this I need to know if I will be able to do payroll.

I've used Excel before, but I don't remember much about the formulas.

If i calculate how much one of my employees makes in one week, is there a way to do a formula in a separate cell that says:

if less than 160 then 0, if 160 or greater but less than 170 then 1, if 170 or greater but less than 180 then 2..... and so on.

I know there is a way to do just "if less than 160, then 0" but will I be able to find a formula that will have all of those specifications (and more) in it?

If you need any more details or whatnot, feel free to ask! I'm not sure I did the best job explaining that!

Answer 1

Assume data is in A1.  Formula in B1 would be:

=If(A1<160,0,If(And(A1>159,A1<170),1,

If(And(A1>169,A1<180),2)))

This should be one complete formula.  YA truncates formulas brutally.

Answer 2

There are a few ways to do that calculation, here's the two most common methods:

Using an IF formula (good for less criteria):

=IF(A1<160, 0, IF(A1<170, 1, IF(A1<180, 2, "too great")))

Using a lookup formula (good for number lists and lookups):

=VLOOKUP(A1, {0,0; 160, 1; 170, 2; 180, "too great"},2, TRUE)

(it will pick the closest number that is less than it. ie, if A1=178 it will pick 170)

Excel would certainly be able to handle payroll.