A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Nested IF & AND functions



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2010, 03:38 AM posted to microsoft.public.excel.worksheet.functions
Stana1027
external usenet poster
 
Posts: 2
Default 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"))))
  #2  
Old April 27th, 2010, 03:46 AM posted to microsoft.public.excel.worksheet.functions
ozgrid.com
external usenet poster
 
Posts: 328
Default 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"))))


  #3  
Old April 27th, 2010, 04:29 AM posted to microsoft.public.excel.worksheet.functions
Stana1027
external usenet poster
 
Posts: 2
Default 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"))))


  #4  
Old April 27th, 2010, 04:50 AM posted to microsoft.public.excel.worksheet.functions
ozgrid.com
external usenet poster
 
Posts: 328
Default 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"))))



  #5  
Old April 27th, 2010, 06:23 AM posted to microsoft.public.excel.worksheet.functions
David Heaton[_2_]
external usenet poster
 
Posts: 7
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.