Excel Formula Help

[PapaJK]

Hi all
Please render some assistance here
On my attached file i have cells ABC merged into 1 and with a number 2 in it and the next cells CDE merged and the number 4 in it.
Cell A2 has number 10.
In cell A3 I put in a formula to multiply the 2 in the merged cells with the 10 in A2 however if I drag the formula to B3 i immediately get a zero.
Is there a way or a formula that I can use so that in cell B3 I get a the answer of (10 x 4) and not the 0.
Basically i want to have in A3 - 20; B3 - 40 and C3 - 60

Thank you in advance

 

[themba990]

Use an abosolute reference for the 10 in A2, so formula becomes '= A1*$A$2'

 

[PapaJK]

Themba990

I have used the absolute reference however when i drag across to B3 then the formula becomes A2*$A$2 which then makes it zero.
I am looking for an equation that can display the answer of CDE (4) by A2(10) immediately B3 without having the zeroes in between.

Regards

 

[Meelperd]

Please upload your Excel file, not PDF, so that we can actually investigate your formulas.

 

[swift412]

In cell A3:
=$A$2 * OFFSET($A$1, 0, (COLUMN()*3)-3)

This will only work if the merged cells in row 1 are always 3 cols at a time.
Also, if you move the cells in row 3 to the right, the offset will change, and that will break it as well...

If you prefer to use INDEX(),
=$A$2 * INDEX($A$1:$I$1, 1, (((COLUMN()-1)*3)+1) )

 

[PapaJK]

Please upload your Excel file, not PDF, so that we can actually investigate your formulas.

Hi Meelperd
I did try to upload the excel file but it was rejected.
I am new at this so maybe there is something i didn't get right

 

[PapaJK]

In cell A3:
=$A$2 * OFFSET($A$1, 0, (COLUMN()*3)-3)

This will only work if the merged cells in row 1 are always 3 cols at a time.
Also, if you move the cells in row 3 to the right, the offset will change, and that will break it as well...

If you prefer to use INDEX(),
=$A$2 * INDEX($A$1:$I$1, 1, (((COLUMN()-1)*3)+1) )

Hi Swift412
This solution actual worked for me.
Thanks a million times

regards

 

[Meelperd]

I am new at this so maybe there is something i didn't get right

Nah, I forgot that new members on MyBB can't upload until you have posted a certain amount of messages, or something like that.

As for Swift, let's give that man a Bells Bains

 

[I.am.Sam]

use an if formula and see if that works. if the cell is blank give a blank else do the multiplication formula

 

[swift412]

Hi Swift412
This solution actual worked for me.
Thanks a million times

regards

Excellent, glad to help