Not sure which formula to use..

[ericwuest]

Hello all... not sure if this is even possible to do in Excel but I
have a worksheet where I am trying to calculate an answer using
multiple conditions. Column A has years of service (1, 5, 10, etc etc
etc). Column B has hours per week (35 or 37.5 depending on where a
person works.) Column C will be the result of the formula.

What I want to do is have a formula calculate an entitlement of time
based on that first column AND second column which could contain a wide
range of information.

ie. if column A equals a number between 1 and 3, and column B equals
35, then multiply the resulting number by "this amount".....

BUT

if column A equals a number between 4 and 10, and column B equals 35,
then multiply the resulting number by "that amount".....

For Example
((Column A=2, Column B=35), therefore the formula would look at column
A, verify that it is between 1 and 3, look at column B verify that it
is 35, and then multiply the result by say the number "5"))

but...

If column A=5, Column B=35, then the formula would look at column A,
see that it equals the second condition ((between 4 and 10)), now
verify against column B and multiply the result by say the number "8".

Initially I had thought of the "If/Then" function, but there are
multiple conditions because it's really "If" this, AND this, then do
"that" but "If" That, AND that, then do "This"....

I can't figure out what formula almost combines the "If/then" function
and allows a mathematical calculation to be attached to the If/Then
condition.

Oh... I need to calculate this for almost 1500 employees so that's the
reason I don't want to do this by hand......

 

[shail]

You didn't specified what is "that number" or "this number".


You can write the formula as

=if(and(a1>=1,a1<=3,b1=35),resulting number*this
number,if(and(a1>=4,a1<=10,b1=35),resulting number*this
number,if(..............)))

so on..

If you could specify what are resulting number and this number it would
be easier for me to write a formula for you.

Thanks

Shail

 

[Eric]

Thanks for your help...

I was able to figure out the first part of the formula using the =IF
statement.
So in my original example, I successfully can get Excel to calculate
"this number" and "That number"

(Your formula below gave me some insight as to how to clean up my
formula (I did not know about using A1>=1 (literal translation of "If
A1 is greater than or less than 1".... )

Based on what you wrote as the formula and what I (think) I want to
translate it to, here's what I came up with:

=IF(AND(F4<4,C4=35),5.85*2),IF(AND(F4>=4,F4<=10,C4=35),8.75*2),IF(AND(F4>10,C4=35),11.67*2)

Literal translation: If cell F4 is less than "4" and cell C4=35, then
Multiply 5.85*2 but
If cell F4 is greater than or equal to "4" and cell F4 is less than or
equal to "10" and cell C4=35, then Multiply 8.75*2 but If cell F4 is
greater than 10 and cell C4=35, then Multiply 11.67*2

Of course my formula is not working and I'm getting the #Value! error.I
could very well just be missing a ( ) somewhere, but I'm not sure...

 

[shail]

Hi Eric,

You should rewrite the formula as

=IF(AND(F4<4,C4=35),5.85*2,IF(AND(F4>=4,F4<=10,C4=35),8.75*2,IF(AND(F4>10,C4=35),11.67*2,0)))

This will help you.

thanks,

Shail

 

[shail]

Hi Eric,

You should rewrite the formula as

=IF(AND(F4<4,C4=35),5.85*2,IF(AND(F4>=4,F4<=10,C4=35),8.75*2,IF(AND(F4>10,C4=35),11.67*2,0)))

This will help you.

thanks,

Shail