Excel Formula Logic Problem,

  • Thread starter Thread starter nander
  • Start date Start date
N

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
 
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
 
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.
 
=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
 
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
 
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
 
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),"")
 
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),"")
 
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
 
=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
 
Back
Top