Nested IF & AND functions

[Stana1027]

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"))))

 

[ozgrid.com]

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

 

[Stana1027]

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.

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



--
Regards
Dave Hawley
www.ozgrid.com

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"))))

 

[ozgrid.com]

RE: 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

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.

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



--
Regards
Dave Hawley
www.ozgrid.com

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"))))

 

[David Heaton]

RE:  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

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.

What is "[Years Service]" and "[Pay Grade]"??????? If another Workbook,
you
need sheet names and cell addresses.
--
Regards
Dave Hawley
www.ozgrid.com
Trying to calculate bonus's for employees working >1 year, and basedon
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 quotedtext -

- 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]!A1>1,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]!A1>1,CHOOSE([Pay Grade]!A1,$Z6$,$Z$7,$Z
$8,"Invalid Pay Grade"),"Not Here Long Enough")

hth

Regards

David