Missing Something REALLY obvious

  • Thread starter Thread starter sbremner
  • Start date Start date
S

sbremner

I think I'm missing something REAL obvious here, can anyone help?

All I need the "Bonus" cell to figure out is the bonus based on th
following.

POINTS (Cell G5) BONUS
11 No Bonus
21 No Bonus
34 No Bonus
43 No Bonus
52 No Bonus
73 20 Pounds
76 20 Pounds
78 20 Pounds
83 20 Pounds
95 100 Pounds
98 100 Pounds

But I can't seem to do it!! ARGHHHH
Can you have a look at the code below and see if you can figure ou
what I'm doing wrong?


Code
 
One way

=VLOOKUP(G5,{0,0;73,20;95,100},2)

format result as pound currency


--

Regards,

Peo Sjoblom


sbremner said:
I think I'm missing something REAL obvious here, can anyone help?

All I need the "Bonus" cell to figure out is the bonus based on the
following.

POINTS (Cell G5) BONUS
11 No Bonus
21 No Bonus
34 No Bonus
43 No Bonus
52 No Bonus
73 20 Pounds
76 20 Pounds
78 20 Pounds
83 20 Pounds
95 100 Pounds
98 100 Pounds

But I can't seem to do it!! ARGHHHH
Can you have a look at the code below and see if you can figure out
what I'm doing wrong?


Code:
Click to expand...
Pounds",IF(G5>=95,"100 Pounds"))))
--------------------



------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
=IF(G5<=52,"No Bonus",IF(G5>=95,"100 Pounds","20 Pounds")) works for me.

However, I probably wouldn't even do it this way. I would suggest setting
up 2 columns of data (Points, Bonus Info) and use VLookup to get the bonus
info instead. The biggest reason is because when the bonus info changes
(and you know it will) you only need to update the info in your table rather
than editing any formulas. Those formulas will only get more convuluted if
more condition states are added, and look how much trouble just 3 condition
states are causing you :-)
It will also be much easier to "prove" to laymen that it is working
properly, and that can mean a big savings in aggravation, *especially* with
bonus/payroll issues.

=VLOOKUP(G5,A2:B12,2)

This assumes Points are in ColumnA and BonusInfo is in ColumnB. In brief:
"find the largest value in column A (Points) that is less than or equal to
G5 and then return the corresponding value from column B (Bonus Info).
Review the documentation for vlookup for further info, there are various
options, including finding only exact matches.

--
George Nicholson

Remove 'Junk' from return address.


sbremner said:
I think I'm missing something REAL obvious here, can anyone help?

All I need the "Bonus" cell to figure out is the bonus based on the
following.

POINTS (Cell G5) BONUS
11 No Bonus
21 No Bonus
34 No Bonus
43 No Bonus
52 No Bonus
73 20 Pounds
76 20 Pounds
78 20 Pounds
83 20 Pounds
95 100 Pounds
98 100 Pounds

But I can't seem to do it!! ARGHHHH
Can you have a look at the code below and see if you can figure out
what I'm doing wrong?


Code:
Click to expand...
Pounds",IF(G5>=95,"100 Pounds"))))
--------------------



------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Yes you are. What happens for values between 53 and 73?

Plus, excel will return the first true result it finds so
order is important. A value of 98 is >= 53 so would
return "20 pounds" in your formula. I think you want to
try something along these lines...

=IF(G5<=52,"no bonus",IF(G5<73,"10 pounds",IF(AND
(G5>=73,G5<=83),"20 pounds","100 pounds")))

I made up the bonus of "10 pounds" for values >52 and <73.
You need to specify what the bonus is really supposed to
be.

Notice the AND in the 3rd IF statement. This says it has
to be >=73 AND <=83.

Also note, you don't need an if for the last condition,
which is >83. If all the other conditions aren't true the
only one left is >83 = "100 pounds"

-----Original Message-----

I think I'm missing something REAL obvious here, can anyone help?

All I need the "Bonus" cell to figure out is the bonus based on the
following.

POINTS (Cell G5) BONUS
11 No Bonus
21 No Bonus
34 No Bonus
43 No Bonus
52 No Bonus
73 20 Pounds
76 20 Pounds
78 20 Pounds
83 20 Pounds
95 100 Pounds
98 100 Pounds

But I can't seem to do it!! ARGHHHH
Can you have a look at the code below and see if you can figure out
what I'm doing wrong?


Code:
Click to expand...
(G5 said:
--------------------



------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
Try this
=IF(G5<=52,"No Bonus",IF(and(G5>52,G5>=73),"20 Pounds",IF
(and(G5>73,G5<=83),"20 Pounds",IF(G5>=95,"100 Pounds"))))

It seems also you are missing ranges 84-94. Didn't know
if you wanted to add that in. If so..follow the same
sequence for the "if(and".

Maxwell
-----Original Message-----

I think I'm missing something REAL obvious here, can anyone help?

All I need the "Bonus" cell to figure out is the bonus based on the
following.

POINTS (Cell G5) BONUS
11 No Bonus
21 No Bonus
34 No Bonus
43 No Bonus
52 No Bonus
73 20 Pounds
76 20 Pounds
78 20 Pounds
83 20 Pounds
95 100 Pounds
98 100 Pounds

But I can't seem to do it!! ARGHHHH
Can you have a look at the code below and see if you can figure out
what I'm doing wrong?


Code:
Click to expand...
(G5 said:
--------------------



------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
Back
Top