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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|