Formula for Calculating if someone gets a bonus

[tonywatsonhelp]

a

b

c

d

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

aa

ab

ac

1

salesperson

jan14

feb14

mar14

apr14

may14

un14

jul14

aug14

sep14

oct14

nov14

dec14

jn15

feb15

mar15

apr1

may15

jun15

jul15

aug15

sep15

oct5

nov15

dec15

bonus due

bonus

2

tom

200

250

300

600

100

100

no

250

3

bobby

100

100

00

100

100

255

yes

4

sue

100

250

00

100

200

450

no

<tbody>
</tbody>

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