Formula for Calculating if someone gets a bonus

[tonywatsonhelp]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[TD]i
[/TD]
[TD]j
[/TD]
[TD]k
[/TD]
[TD]l
[/TD]
[TD]m
[/TD]
[TD]n
[/TD]
[TD]o
[/TD]
[TD]p
[/TD]
[TD]q
[/TD]
[TD]r
[/TD]
[TD]s
[/TD]
[TD]t
[/TD]
[TD]u
[/TD]
[TD]v
[/TD]
[TD]w
[/TD]
[TD]x
[/TD]
[TD]y
[/TD]
[TD]z
[/TD]
[TD]aa
[/TD]
[TD]ab
[/TD]
[TD]ac
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]salesperson
[/TD]
[TD]jan14
[/TD]
[TD]feb14
[/TD]
[TD]mar14
[/TD]
[TD]apr14
[/TD]
[TD]may14
[/TD]
[TD]un14
[/TD]
[TD]jul14
[/TD]
[TD]aug14
[/TD]
[TD]sep14
[/TD]
[TD]oct14
[/TD]
[TD]nov14
[/TD]
[TD]dec14
[/TD]
[TD]jn15
[/TD]
[TD]feb15
[/TD]
[TD]mar15
[/TD]
[TD]apr1
[/TD]
[TD]may15
[/TD]
[TD]jun15
[/TD]
[TD]jul15
[/TD]
[TD]aug15
[/TD]
[TD]sep15
[/TD]
[TD]oct5
[/TD]
[TD]nov15
[/TD]
[TD]dec15
[/TD]
[TD]bonus due
[/TD]
[TD] bonus
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]tom
[/TD]
[TD]200
[/TD]
[TD]250
[/TD]
[TD]300
[/TD]
[TD]600
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]no
[/TD]
[TD]250
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]bobby
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]00
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]255
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]sue
[/TD]
[TD]100
[/TD]
[TD]250
[/TD]
[TD]00
[/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]450
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]no
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi everyone,

Bit more of a challenge for you to get your teeth into!

I have a spreedsheet where I input total monthly sales of each person and this runs on a two year basis starting form January 2014 to dec 2015 as above,
So I end up with the salespersons name followed by up to 24 money values.

I need a way to calculate if the person has a bonus for his latest one month sales
the rules are that if his most recent sale is greater than AB2 (250) he gets a bonus but only if he has not reached 250 before,

So to me the formula would read:
if tom's (a1) latest sales figure is greater than AB2 then "bonus", but only if no other sales figures of his have already been greater then AB2 (in this case 250) otherwise "NO Bonus"

can someone please let me have this formula?

thank you

 

[VBA Geek]

in Z2

=CHOOSE(1+AND(LOOKUP(9.99999999999E+307,$B2:$Y2)>=250,COUNTIF($B2:$Y2,">=250")=1),"no bonus", "bonus")

 

[tonywatsonhelp]

Hi,
that's great, would never have been able to get that so thank you very much

 

[tonywatsonhelp]

Hi,

Thanks formula works perfectly,
have another calculation and wondered if you could edit the formula above so I can see how it changes

This time I want it to just look long Line B and see if the last entered data = 199 or above, if so then "Current" if not "Not Current"

thanks

 

[VBA Geek]

you mean down column B?

IF(LOOKUP(9.99999999999E+307,$B:$B)>=199, "CURRENT", "NOT CURRENT")

I dont know the actual lenght of your data, you can adjust it so it looks to a smaller range and not the whole columns, ie $B$1:$B$1000

 

[tonywatsonhelp]

That is brilliant, thank you again