Fixed a formula in a cell which can be copied

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
71
ABCDEF
1BuyProduct A3
2BuyProduct A3
33
4SellProduct A1=(1 x 3)
5SellProduct A2=(2 x 3)
6SellProduct A4=(4 x 3)
7
8BuyProduct B14
97
10SellProduct B5=(5 x 7)
11SellProduct B6=(6 x 7)
12

<tbody>
</tbody>


Hi Guys, I had tried to resolve the above formula for 2 days and unable to get a workable formula.

What I am trying to achieve is, I wish to fix a formula where I can fix the calculation moving downwards to capture the source at column E. And it can be copy straight away to the next few rows, and the formula will continue to capture what is the next multiply factor on column E.

Example, row D4,D5,D6 are fixed to multiply with the cells at E3
and thereafter the formula can be copied continuously to the bottom of the cells, and row D10, D11 will automatically multiply with the cells at E9

Column A consist of the transaction nature
Column B consist of product name
Column C consist of the cost price
Column D consist of the total costs
Column E is the average of total purchase costs

It seems like only macro can do it, but i would like to keep it simple as possible for the next user to understand how is the whole calculation was derived from. But if simple macro which can helps, i would like to know how to do it as well.

Appreciate if anyone could advise on a workable formula to capture the above scenario.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
A non-volatile formula alternative is
D4: =C4*LOOKUP(9.99E+307,E$1:E3)
 
Upvote 0
A slight variation that you might consider would be to insert a new column (column E below) then the formula in E4 could be just copied all the way down the column instead of copy/paste into individual sections.

<b>Multiply</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:42px;" /><col style="width:77px;" /><col style="width:28px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:28px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">12</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">7</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">35</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">42</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E4</td><td >=IF(C4="","",C4*LOOKUP<span style=' color:008000; '>(9.99E+307,F$1:F3)</span>)</td></tr></table></td></tr></table>


.. or even this one (assuming column F value will never be zero).

<b>Multiply 2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:42px;" /><col style="width:77px;" /><col style="width:26px;" /><col style="width:33px;" /><col style="width:33px;" /><col style="width:26px;" /><col style="width:27px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">35</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">42</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E4</td><td >=IF(C4="","",C4*IF<span style=' color:008000; '>(F3,F3,E3/C3)</span>)</td></tr></table></td></tr></table>
 
Last edited:
Upvote 0
A non-volatile formula alternative is
D4: =C4*LOOKUP(9.99E+307,E$1:E3)

OMG!! Thanks Peter, it works like a charm!!! Thank you Sir, it fully fullfill my needs on my sheet.

Meanwhile do you mind further elaborate what is the 9.99E+307 is referring?
 
Upvote 0
A slight variation that you might consider would be to insert a new column (column E below) then the formula in E4 could be just copied all the way down the column instead of copy/paste into individual sections.

Multiply

ABCDEF
1BuyProduct A 3
2BuyProduct A 3
3 3
4SellProduct A1 3
5SellProduct A2 6
6SellProduct A4 12
7
8BuyProduct B 14
9 7
10SellProduct B5 35
11SellProduct B6 42
12

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:42px;"><col style="width:77px;"><col style="width:28px;"><col style="width:64px;"><col style="width:64px;"><col style="width:28px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E4=IF(C4="","",C4*LOOKUP(9.99E+307,F$1:F3))

<tbody>
</tbody>

<tbody>
</tbody>



.. or even this one (assuming column F value will never be zero).

Multiply 2

ABCDEFG
1BuyProduct A 3
2BuyProduct A 3
3 3
4SellProduct A1 3
5SellProduct A2 6
6SellProduct A4 12
7
8BuyProduct B 14
9 7
10SellProduct B5 35
11SellProduct B6 42
12

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:42px;"><col style="width:77px;"><col style="width:26px;"><col style="width:33px;"><col style="width:33px;"><col style="width:26px;"><col style="width:27px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E4=IF(C4="","",C4*IF(F3,F3,E3/C3))

<tbody>
</tbody>

<tbody>
</tbody>

I really wish I could redesign the excel actually. But due to the excel was previously designed by my employer, I wouldn't want add/remove any column to avoid any misunderstanding.

Thanks for your suggestion thou, I actually safe keep your suggested formula for any future needs. Appreciated.
 
Upvote 0
OMG!! Thanks Peter, it works like a charm!!! Thank you Sir, it fully fullfill my needs on my sheet.

Meanwhile do you mind further elaborate what is the 9.99E+307 is referring?
You're welcome. :)

The 9.99E307 is just a very big number (999 followed by 305 zeros). When LOOKUP looks down column E (from the top down to the row above where the formula is) and cannot find that big number or anything bigger, it returns the last number that it did encounter. So in rows 4:6 the last number in column E is 3 but in rows 10:11 the last number encountered in column E is 7

Since you cannot change the layout, you could use this version of my other suggestion & copy to the other relevant rows.
The formula below the screen shot assumes that the relevant number in column E will never be 0. If it can be 0 then the formula could change to
=C4*IF(F3="",E3/C3,F3)

<b>Multiply 2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:42px;" /><col style="width:77px;" /><col style="width:26px;" /><col style="width:33px;" /><col style="width:33px;" /><col style="width:26px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product A</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">12</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">Buy</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">7</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">35</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">Sell</td><td style="font-size:10pt; ">Product B</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">42</td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E4</td><td >=C4*IF(F3,F3,E3/C3)</td></tr></table></td></tr></table>



BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.
 
Upvote 0
Thanks Peter, for the alternative formula.

Noted on the message quote function as well, not fond with forum web thingy, apologies on that, will take note for future post. Appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top