offset issue

[bluepenink]

hello

basic logic issue

=OFFSET((SUMPRODUCT(--('Bonus Calculator'!$C$9:$C$21<=$D$44),--('Bonus Calculator'!$E$9:$E$21>=$D$44),'Bonus Calculator'!$G$9:$G$21)),-1)

basically, i want the formula to lookup up row from the sumproduct answer.

currently getting an issue. pls help!

 

[Jonmo1]

Offset requires a RANGE reference...
Sumproduct returns a NUMBER...


Try this (notice the offset of column G by 1 row from the other columns..)

=SUMPRODUCT(--('Bonus Calculator'!$C$9:$C$21<=$D$44),--('Bonus Calculator'!$E$9:$E$21>=$D$44),'Bonus Calculator'!$G$8:$G$20))

 

[bluepenink]

Hello


thxs for your quick response.

basically, in

D46 = 60% (worksheet name "Performance")

in cell D51 i would like the offset to be dependent on the value in D46.

so if D46 changes to 80%, i would like the offset to work and get the percentage above 90%

i want the values of the percentage in workshet "Calculator G9:G21"

ur thoughts? thxs so much.

 

[bluepenink]

i know this is wrong

but something along the lines of this..not sure how to tackle this or logically place the formula

=OFFSET(D46,'Bonus Calculator'!$G$9:$G$21,-1,)

 

[bluepenink]

hello guys

any suggestions?

 

[bluepenink]

hello

can someone help me with this

=OFFSET(INDIRECT("Bonus Calculator"&MATCH($D$46,'Bonus Calculator'!G9:G21,0)),1,)

 

[bluepenink]

=OFFSET(INDEX('Bonus Calculator'!G9:G21,MATCH('DSM & SM Performance'!$D$46,'Bonus Calculator'!G9:G21,0),1,0)

having some trouble with the logic here guys..any solutions?

 

[Jonmo1]

Did you try my suggestion?

 

[bluepenink]

thx u sir....i thought it was not looking for the offset, appreciate and pls ignore my ignorance!