Forum Discussion
Solved
=sum formula with variable values
I have a data set that has five main values and each value has a different monetary payout to it. I need a to write a formula based on the five different values. For example:
Product Amount Payout
A 100.00 =if A, then amount * .05
B 100.00 =if B, then amount * .08
C 100.00 =if C, then amount * .17
D 100.00 =if D, then amount * .55
E 100.00 =if E, then amount * .75
The Product column will always offer the same values. The amount column may change but the payout column will not. What's the best way to write this query?
RazmigVarsam You didn't enter any formulas. Entered them for you. Is that what you want?
See attached.
10 Replies
RazmigVarsam I suggest you make a lookup table where you list the products and payout factors. Then, use a lookup function to pick-up the correct factor and multiply that by the amount. The attached file contains two examples. One with XLOOKUP and another with VLOOKUP. See which one works for you.
Riny_van_Eekelen I tried using your formula on my sheet it didn't work. Here's what my template looks like. The first box was my vlookup pivot where I have a total of five products and the two different payout percentages noted by NB and R. The second grid below that is the payout sample numbers noted in the Amount column. What am I missing here?
Product NB R A 9.00% 9.00% H 14.00% 10.00% S 15.00% 15.00% C 12.50% 12.50% L 55.00% 5.00% Product Amount NB R A $ 1,000.00 $ - $- H $ 5,353.00 $ - $- S $ 2,562.00 $ - $- C $10,000.00 $ - $- L $ 1,000.00 $ - $- RazmigVarsam Can't tell what's wrong without the file and seeing the formula used. Sorry!
