Untitled.png
need an expert on this please!
am trying to compute staff's contribution in F2 based on criteria B2, C2, D2 AND E2.
1) if B2 = "Y", F2 = "SPR"
2) if D2 = "N", F2 = 0 then if all FALSE,
3) search C2 within B8:E8 of CON'EE table A7:E12 and search E2 in A9:A12, both just need the closest match and return intersecting value
example #1:
Staff A, Age 39 and Gross Pay $650, F2 will return value (B10*(E2-500)) and TRUNC -1
example #2
Staff B, Age 68 and Gross Pay $6800, F2 will return (E12)
*if return value falls under Row 9 or Row 12, = (intersection cell value)
*if return value falls under Row 10, = (intersection cell value*(Gross-500)) and TRUNC -1
*if return value falls under Row 11, = (intersection cell value*Gross) and TRUNC -1
hope the above is understood to you all. really having a hard time trying to get this right!! really appreciate any help now.
thank you!!!
Bookmarks