Sep 19 2022 09:23 PM
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?
Sep 19 2022 09:39 PM
@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.
Sep 21 2022 09:44 AM
@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 | $ - | $- |
Sep 21 2022 11:21 AM
@RazmigVarsam Can't tell what's wrong without the file and seeing the formula used. Sorry!
Sep 21 2022 11:29 AM
@Riny_van_Eekelen I actually don't have a formula other than the two you suggested. I can't seem to get that formula you provided in the sample file to work for the data grid below.
Product | NB | R | |
A | 5.00% | 10.00% | |
H | 9.00% | 5.00% | |
S | 10.00% | 7.00% | |
C | 6.00% | 6.00% | |
L | 25.00% | 5.00% | |
NB | R | ||
A | $ 1,000.00 | $ - | $- |
H | $ 5,353.00 | $ - | $- |
S | $ 2,562.00 | $ - | $- |
C | $10,000.00 | $ - | $- |
L | $ 1,000.00 | $ - | $- |
Sep 21 2022 11:44 AM
Sep 21 2022 12:00 PM
Solution@RazmigVarsam You didn't enter any formulas. Entered them for you. Is that what you want?
See attached.
Sep 21 2022 02:35 PM
Sep 21 2022 07:40 PM
Sep 21 2022 12:00 PM
Solution@RazmigVarsam You didn't enter any formulas. Entered them for you. Is that what you want?
See attached.