Spreading a value over multiple colums/rows

[jorge1]

I was wondering if you can point me in the right direction.

I need to have a macro of formula that would take a cell's value (value1)and spread it over equally over a number (value2) of columns across

A B C D E ...
1 $300 3 $100 $100 $100
2 $20 4 $5 $5 $5 $5
etc.

The expectation is to place a formula like this:
=dividevalue(A1,A3) in Col3 and it does the rest magically!

Is there a preset formula to do this?
Thanks!

 

[Peter_SSs]

Yes, it's clearer but considerably more complex! I think a macro approach would be more feasible now but would still be a considerable undertaking.

BTW, could any of the figures in the upper table be > 1? For example, could you sell two (or more) Sevice 4s in col 6?

 

[jorge1]

Yes - the figures can be > 1

Mmm... I was really trying to avoid the macro rout, because of maintainability... but I totally understand if that is the way to go...

Many thanks for the help!
J

 

[Peter_SSs]

Yes - the figures can be > 1

Mmm... I was really trying to avoid the macro rout, because of maintainability... but I totally understand if that is the way to go...

Many thanks for the help!
J

Well, that's just my view and it's an undertaking that I don't think I can justify putting that much time to on a free forum like this, sorry. Pity, a few posts ago I thought we had a very satisfied customer.

MANY, many thanks! You've made my week

Perhaps somebody else might come along and see an easy way to do it, I don't know?

 

[hartsheldon]

jorge1

Welcome to the MrExcel board!

For a formula approach, copy the formula below across and down.

Spread

	A	B	C	D	E	F	G	H

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF(COLUMNS($C2:C2)>$B2,"",$A2/$B2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you for this. It worked like a charm. You made my week!

 

[Peter_SSs]

Thank you for this. It worked like a charm. You made my week!

Cheers. Glad the solution is still being useful 10 years later.

.. and Welcome to the MrExcel Board!