SOLVED

=sum formula with variable values

Occasional Contributor

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?

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?

 

ProductNBR 
A9.00%9.00% 
H14.00%10.00% 
S15.00%15.00% 
C12.50%12.50% 
L55.00%5.00% 
    
    
    
    
ProductAmountNBR
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!

@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.

 

ProductNBR 
A5.00%10.00% 
H9.00%5.00% 
S10.00%7.00% 
C6.00%6.00% 
L25.00%5.00% 
    
    
    
    
  NBR
A $  1,000.00 $      -   $-  
H $  5,353.00 $      -   $-  
S $  2,562.00 $      -   $-  
C $10,000.00 $      -   $-  
L $  1,000.00 $      -   $-  

@RazmigVarsam Can you share your file?

I don't have access to upload a file. Please try this link, https://1drv.ms/x/s!Ajg9jrbpQkHdgdcj5xtlsmvwRaJ16g?e=IYGtIl.
best response confirmed by Hans Vogelaar (MVP)
Solution

@RazmigVarsam You didn't enter any formulas. Entered them for you. Is that what you want?

See attached.

That worked, thank you so much.
Any idea why when I drag the formula down in the cell, the formula values change?
nvm, I was able to figure it out. Thank you so much for your help.