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!

 

[stanleydgromjr]

jorge1,

With your data beginning in cell A1:

Excel Workbook
	A	B	C	D	E	F	G	H
1	$300	3
2	$20	4
3	$300	4
4	$100	3
5	$100	6
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1	$300	3	$100.00	$100.00	$100.00
2	$20	4	$5.00	$5.00	$5.00	$5.00
3	$300	4	$75.00	$75.00	$75.00	$75.00
4	$100	3	$33.33	$33.33	$33.33
5	$100	6	$16.67	$16.67	$16.67	$16.67	$16.67	$16.67
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit
Sub DivideValue()
    Dim LR&, LoopCtr1&, LCol&, Ans#
    Application.ScreenUpdating = False
    LR& = Cells(Rows.Count, "A").End(xlUp).Row
    For LoopCtr1& = 1 To LR& Step 1
        Ans# = Cells(LoopCtr1&, "A").Value / Cells(LoopCtr1&, "B").Value
        LCol& = Cells(LoopCtr1&, "B").Value - 1
        With Range(Cells(LoopCtr1&, 3), Cells(LoopCtr1&, 3 + LCol&))
            .Value = Ans#
            .NumberFormat = "$#,##0.00"
        End With
    Next LoopCtr1&
    Application.ScreenUpdating = True
End Sub

Then run the "DivideValue" macro.


Have a great day,
Stan

 

[Peter_SSs]

jorge1

Welcome to the MrExcel board!

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

Excel Workbook
	A	B	C	D	E	F	G	H
1
2	300	3	100	100	100
3	20	4	5	5	5	5
4
Spread

 

[Peter_SSs]

Here is another macro approach. Like my previous example, it assumes data actually starts in A2. Modify code if that is not the case.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Spread()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>        c.Offset(, 2).Resize(, c.Offset(, 1).Value).Value = _<br>            c.Value / c.Offset(, 1).Value<br>    <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jorge1]

First of all THANK YOU - GREAT responses! This is a fantastic resource and appreciate the help...

The formula approach works the best for my purpose, but I wonder how can I make it work with the following data / scenario... (I have made all of the calculations by hand) .

http://picasaweb.google.com/jorper98/Misc1#5257227833400088642

 

[Peter_SSs]

I would use some helper columns (C, D & E below) which could be hidden if you want.

Formulas in C2 & D2 copied down. E2 down remains blank.
Formula in F2 is copied across and down.

Just check that the results in the last row are what you would expect. (You didn't give an example where the Time was > 1 with a decimal other than .5)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Amount	Time	Cols	Base Amt	Blank
2	9,000	0.3	1	9,000		9,000
3	36,000	0.8	1	36,000		36,000
4	72,000	1.5	2	48,000		48,000	24,000
5	125,000	2.5	3	50,000		50,000	50,000	25,000
6	576,000	6	6	96,000		96,000	96,000	96,000	96,000	96,000	96,000
7	25,000	2	2	12,500		12,500	12,500
8	50,000	6	6	8,333		8,333	8,333	8,333	8,333	8,333	8,333
9	85,000	10	10	8,500		8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500
10	36,000	1.8	2	20,000		20,000	16,000
Spread

 

[jorge1]

MANY, many thanks! You've made my week -
Cheers!
Jorge

 

[jorge1]

A bit more to add to the challenge...
Need to add another set of variables to this one. They are multipliers which can start at any column, and then expand from that point forward for as many columns as required...
Something like this:

It would be great to stay with formulas rather than macros...
I understand that the number of columns will need to be fixed (ok to loose values beyond that).
Note the item in blue. It is there to note that the value is the combination of intersecting calculations which will be added together in this cell.

Thanks!
J

 

[Peter_SSs]

I'm afraid I don't understand how any of the results are arrived at.

 

[jorge1]

Sorry that I was not clear.

The idea is that an item can appear at any time within the time period, and it has to spread over multiple columns forward.


(Note: Row labeled: Service 1 / Column labeled: 4 should have been a 0).
Corrected/Updated chart follows:

Rows Services 1 & 2 fit within one month, so no need to spread across more than each column.

Row Service 3 spreads over 2 months, so that is why it is $48K and $24K

Service 4; Col 4 spreads over 3 months: $50k, $50K and $25K.
Another Service 4 is sold in Col 6, it starts 50K, 50K, 25K
Service 4, Col 6 would have $25K + 50K = $75K.

In Service 5; Col 9+10 there is an overlap as well and that is why the amounts are doubled


Hopelfully this is a bit more clearer.
(Does this make more sense?)