Nested IF & AND functions
 

Trying to calculate bonus's for employees working 1 year, and based on their
pay grade. Yet I'm getting #value, and Excel says I'm using the data type?
help please!

=IF(AND([Years Service]=1, "NE"), IF([Pay Grade]=1,$Z$6,IF([Pay Grade]=2,
$Z$7, IF([Pay Grade]=3, $Z$8, "Invalid pay grade"))))

Nested IF & AND functions
 

What is "[Years Service]" and "[Pay Grade]"??????? If another Workbook, you
need sheet names and cell addresses.



--
Regards
Dave Hawley
www.ozgrid.com



"Stana1027" wrote in message
...
Trying to calculate bonus's for employees working 1 year, and based on
their
pay grade. Yet I'm getting #value, and Excel says I'm using the data type?
help please!

=IF(AND([Years Service]=1, "NE"), IF([Pay Grade]=1,$Z$6,IF([Pay Grade]=2,
$Z$7, IF([Pay Grade]=3, $Z$8, "Invalid pay grade"))))

Nested IF & AND functions
 

I've got "[Years Service]" and "[Pay Grade]" in the same workbook with sheet
names and cell addresses.
The AND calculation is whats causing the problems...and my book doesn't show
the AND function used with the IF function.

"ozgrid.com" wrote:

What is "[Years Service]" and "[Pay Grade]"??????? If another Workbook, you
need sheet names and cell addresses.



--
Regards
Dave Hawley
www.ozgrid.com



"Stana1027" wrote in message
...
Trying to calculate bonus's for employees working 1 year, and based on
their
pay grade. Yet I'm getting #value, and Excel says I'm using the data type?
help please!

=IF(AND([Years Service]=1, "NE"), IF([Pay Grade]=1,$Z$6,IF([Pay Grade]=2,
$Z$7, IF([Pay Grade]=3, $Z$8, "Invalid pay grade"))))

Nested IF & AND functions
 

I've got "[Years Service]" and "[Pay Grade]" in the same workbook with
sheet
names and cell addresses.

Not from the formula you are showing!



--
Regards
Dave Hawley
www.ozgrid.com
"Stana1027" wrote in message
...
I've got "[Years Service]" and "[Pay Grade]" in the same workbook with
sheet
names and cell addresses.
The AND calculation is whats causing the problems...and my book doesn't
show
the AND function used with the IF function.

"ozgrid.com" wrote:

What is "[Years Service]" and "[Pay Grade]"??????? If another Workbook,
you
need sheet names and cell addresses.



--
Regards
Dave Hawley
www.ozgrid.com



"Stana1027" wrote in message
...
Trying to calculate bonus's for employees working 1 year, and based on
their
pay grade. Yet I'm getting #value, and Excel says I'm using the data
type?
help please!

=IF(AND([Years Service]=1, "NE"), IF([Pay Grade]=1,$Z$6,IF([Pay
Grade]=2,
$Z$7, IF([Pay Grade]=3, $Z$8, "Invalid pay grade"))))

Nested IF & AND functions
 

On Apr 27, 1:50*pm, "ozgrid.com" wrote:
*I've got "[Years Service]" and "[Pay Grade]" in the same workbook with
sheet

names and cell addresses.

Not from the formula you are showing!

--
Regards
Dave Hawleywww.ozgrid.com"Stana1027" wrote in message

...



I've got "[Years Service]" and "[Pay Grade]" in the same workbook with
sheet
names and cell addresses.
The AND calculation is whats causing the problems...and my book doesn't
show
the AND function used with the IF function.

"ozgrid.com" wrote:

What is "[Years Service]" and "[Pay Grade]"??????? If another Workbook,
you
need sheet names and cell addresses.

--
Regards
Dave Hawley
www.ozgrid.com

"Stana1027" wrote in message
...
Trying to calculate bonus's for employees working 1 year, and based on
their
pay grade. Yet I'm getting #value, and Excel says I'm using the data
type?
help please!

=IF(AND([Years Service]=1, "NE"), IF([Pay Grade]=1,$Z$6,IF([Pay
Grade]=2,
$Z$7, IF([Pay Grade]=3, $Z$8, "Invalid pay grade"))))- Hide quoted text -

- Show quoted text -


I'm confused by
IF(AND([Years Service]=1, "NE")

it looks like you are wanting to return 'NE' if the [Years of Service]
<1.

an example format for AND is

=AND(A1=1,A2=1)

if both A1 and A2= 1 then the formula will return TRUE, otherwise it
returns FALSE
you arent listing multiple conditions therefore Excel cant resolve the
formula as you have written it.


I do think there is some of your formula missing. As Dave asked 'What
does [Years of Service] refer to ?

Anyway i'll all my own cell references to help you out


from the description you have given i can see to options for you

=IF([Years of Service]!A11,IF([Pay Grade]!A1=1,$Z$6,IF([Pay Grade]!
A1=2,$Z$7,IF([Pay Grade]!A1=3,$Z$8,"Invalid Pay Grade"))),"Not Here
Long Enough")

or

=IF([Years of Service]!A11,CHOOSE([Pay Grade]!A1,$Z6$,$Z$7,$Z
$8,"Invalid Pay Grade"),"Not Here Long Enough")

hth

Regards

David