Multiply A Constant Number By A Changing Variable

[NewToThis86]

Hello All!
My deepest apologies if this has already been asked, as I am sure it has, but I am at work and do not have the time to hunt in the past threads.

I am not very Excel savvy as I am more accustomed to film work, that said, I am doing some data entry and want to make my job a touch easier.

I am creating a budget tracker to keep our expenses in line, and I am stuck on how to create a formula that will do what I want. Essentially, I need one that will multiply our constant price for certain goods (10.18, 112, 32.64) by the changing variable of how many items they receive (1, 2 ,3, etc). I've tried creating 3 cells containing the constants and tried to create a formula that way, but it ends up being circular.

Help me, Mr Excel. You're my only hope.

 

[AlanY]

with the no of items in A1, A2 & A3

=SUMPRODUCT(A1:C1,{10.18,112,32.64})

 

[NewToThis86]

with the no of items in A1, A2 & A3

=SUMPRODUCT(A1:C1,{10.18,112,32.64})

So on my sheet I have 10.18 in B1, 112 in B2, and 32.64 in B3 and I am trying to get the product into CM12 currently. So when I type 2 (for the amount of items received) in CM12, I want the product of 2 and 10.18 to appear, 3 in CM13, I want the product of 3 and 32.64 to appear, and 2 in CM14, I want the product of 2 and 112 to show up.

 

[AlanY]

So on my sheet I have 10.18 in B1, 112 in B2, and 32.64 in B3 and I am trying to get the product into CM12 currently. So when I type 2 (for the amount of items received) in CM12, I want the product of 2 and 10.18 to appear, 3 in CM13, I want the product of 3 and 32.64 to appear, and 2 in CM14, I want the product of 2 and 112 to show up.

a bit confused here.

lets say the prices of A, B & C are 10.18, 112 & 32.64 repectively.

for the text in RED, is it 2 items of product A?

for the text in BLUE, is it 3 items of product C (as price is 32.64)?

and 2 in CM14, the product of 2 and 112, 2 items of product C?

 

[NewToThis86]

Yes, that appears right.
So Item A is priced at 10.18 and a customer orders 3 of those. In the cell I want to be able to type "3" and the formula to calculate "10.18*3", and then the same for the corresponding prices.
I know this sounds really confusing, at least it is for me to explain, but it has to be pretty straightforward.

 

[AlanY]

in that case

Excel 2012
	CM	CN
11	No of Items	Total
12	2	20.36
13	3	97.92
14	2	224
Sheet5

Excel 2012
	CN
12	20.36
13	97.92
14	224
Sheet5

Cell Formulas
Range	Formula
CN12	=IF(CM12>0,$B$1,0)*CM12
CN13	=IF(CM13>0,$B$3,0)*CM13
CN14	=IF(CM14>0,$B$2,0)*CM14

 

[AlanY]

right, you wanted to input 3 in CM13, say, then the result of 97.92 written on CM13 itself?
i don't think that's possible, at least for me.

 

[NewToThis86]

The problem with that is that I'd have to add an extra column for number of items to each corresponding column (each column is a dated event). I have been utilizing several sources to figure this out, and have realized that whenever I enter the number of items, the formula gets erased. So, in that case, it has been suggested to add a macro to each cell.

 

[AlanY]

The problem with that is that I'd have to add an extra column for number of items to each corresponding column (each column is a dated event). I have been utilizing several sources to figure this out, and have realized that whenever I enter the number of items, the formula gets erased. So, in that case, it has been suggested to add a macro to each cell.

now i see what you're trying to achieve.
to be quite honest even if it's doable without an extra column then it has no audit trail for the input/output and just becomes a blackbox.

 

[NewToThis86]

Yeah
I took this spreadsheet over from someone and it is set up horribly. I'm glad to see that I wasn't missing something simple. Thanks for your help!!