Excel Formula Logic Problem,

[nander]

See attached excel file. I'd like a single cell formula that returns on
of the values C4-C9 if a value in C13-C18 is met.

In a single cell I'm trying to evaluate the value of C13 across th
range of A2-A9. So I think the forumale should look something like
but its not working. Please help

=IF(C13<=.90,C2),=IF(AND(C13>=.90,C13>=.95),C3),=IF(AND(C13>.96,C13<100,C3)=IF(C13=100,C4)

A B C
D
1 Store Incentive Manager
2 90% LY Sales ($1,500.00)
3 95% LY Sales ($1,000.00)
4 100% LY Sales $0.00
5 105% LY Sales $1,000.00
6 110% LY Sales $1,500.00
7 115% LY Sales $2,000.00
8 120% LY Sales $2,500.00
9 125% LY Sales $3,500.00
10
11
12 MONTH 2004 estim 2003 actual
13 Jan 100,857.26 92.85% 108,629.32
14 Feb 102,985.00 114.63% 89,837.51
15 Mar 115,278.00 101.53% 113,542.94
16 Apr 139,321.00 111.20% 125,284.01
17 May 169,425.00 107.41% 157,736.04
18 Jun 189,202.00 113.70% 166,401.2

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44261

 

[Frank Kabel]

Hi
for evaluating C3 try
=VLOOKUP(C13,$A$2:$C$9,3,1)
this will return the value of column C for the percentange in column A
that is <= your lookup value in C13
copy this formula down for your values in C14-C19

 

[Norman Harker]

Hi Nander!

Re:
=IF(C13<=.90,C2),=IF(AND(C13>=.90,C13>=.95),C3),=IF(AND(C13>.96,C13<10
0,C3)=IF(C13=100,C4)

Try:
=IF(C13<=.9,C2,IF(C13<100,C3,IF(C13=100,C4,"")))

I've assumed and note various errors in your code.

Specifically:

You can't use =IF(etc) in a nested function; just drop the =
In your first AND you have C13>=.9 which is already covered by C13<=.9
In your first AND the second statement is otiose because >=.9 also
covers >=.95
You are not handling >.95 <=.96
You are not handling >100

Remember that IF functions are handled sequentially and you don't need
to handle cases already covered earlier in the function.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andrew]

Try this

=VLOOKUP(C13,$A$2:$C$9,3)

where c13 is the cell you want to evaluate.

 

[nander]

=IF(C13<=0.9,C2,IF(C13<100,C3,IF(C13=100,C4,if(c13<105,c5,if(c13<110,c6,if(c13<115,c7,if(c13<120,c8,IF(C13<125,C9""))))))))

When I key this formula the C9"" is highlighted.

this is the goal. to evaluate all of the incentives against January'
value.

If the manager is 90% of his goal he goes in the hole $1500. However i
he is between 91% and 95% of his goal he goes in the hole $1000. If h
is equal to last year he gets nothing. If he is 105% he gets $1000 etc



Im not familiar with Vlookup, but if it can be written to achive th
same goal I'd like to know how.

Norman
Dothan, Alabam

 

[nander]

Now that I thought more about the VLOOKUP solution. I belive that is th
answer. thank yo

 

[nander]

Another question. I'm linking these incentive values to my incom
statement. So I only want an incentive greater than 0 to appear in th
cell named Benefits/Incentive. How do I filter the resulting calcuatio
=VLOOKUP(C13,$A$2:$C$9,3) so that a value of greater than 0 wil
appear

 

[Frank Kabel]

Hi
try
IF(VLOOKUP(C13,$A$2:$C$9,3)>0,VLOOKUP(C13,$A$2:$C$9,3),"")

 

[nander]

Frank, thanks again!

IF(VLOOKUP(C13,$A$2:$C$9,3)>0,VLOOKUP(C13,$A$2:$C$9,3),"")

Works but for other months I get the #NAME? error.


See attached file look in the Amount Paid Column

*Hi
try
IF(VLOOKUP(C13,$A$2:$C$9,3)>0,VLOOKUP(C13,$A$2:$C$9,3),"")

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44298

 

[nander]

Andrew, Frank & Norman H thanks to all of you for your help.
appreciate the help very much

 

[Frank Kabel]

Hi
quite easy. You use the formula
=IF(VLOOKUP(C13,$A$2:$C$9,3)>0,VLOOOKUP(C13,$A$2:$C$9,,),"")
tow small mistakes:
- VLOOKUP with 3 'O's
- you should qualify the 3rd and 4th parameter.
Change the formula in F13 to
=IF(VLOOKUP(C13,$A$2:$C$9,3,1)>0,VLOOKUP(C13,$A$2:$C$9,3,1),"")
and copy down

 

[nander]

What range do I use to evaluate the added column of Asst Mgr?
See attached file. thanks again.

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=443146

 

[Frank Kabel]

Hi
first: you should try to express your issue in plain text - most people
do not look at attachments
To your problem. Use
IF(VLOOKUP(C13,$A$2:$D$9,4,0)>0,VLOOKUP(C13,$A$2:$D$9,4,0),"")

 

[nander]

Okay, I tried the formula an get the #N/A erro

 

[nander]

Does the number 4 refer to the Column D? After the 4 what does the
represent

 

[Frank Kabel]

Hi
sorry, so used to using VLOOKUP with exact matches I messed up the 4th
parameter
Try
IF(VLOOKUP(C13,$A$2:$D$9,4,1)>0,VLOOKUP(C13,$A$2:$D$9,4,1),"")

 

[Frank Kabel]

Hi
yes the 4th reflects to the 4th column in the specified range. The 0
indicates to search for an exact match (same as FALSE). You may have a
look at the Excel help (search for 'VLOOKUP') to learn more about the
different parameters

 

[nander]

=IF(VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$G$10,6,1)>0,VLOOKUP(Sales!$D$3,'Incentiv
(2)'!$A$5:$G$10,6,1),0)

This formulae returns a #N/A When sales are 105% and below I want a
instead. What needs to be changed in the above formula?

This is the incentive(2) worksheet this formula references

Mature Store Ast Sales Office Warehouse
Store Incentive Manager Manager Associate
45% 50% 100%
105% LY Sales $0.00 $0.00 $0.00
106% LY Sales $300.00 $150.00 $100.00 $50 $50
108% LY Sales $400.00 $200.00 $150.00 $50 $50
110% LY Sales $600.00 $300.00 $200.00 $50 $50
115% LY Sales $800.00 $400.00 $250.00 $50 $50
120% LY Sales $1,000.00 $500.00 $300.00 $50 $5