How to make my formula more efficient

[yuzi]

Hello,

I'm trying to create a formula that's most efficient. Right now there's too much manual work. I'm trying to create a formula that takes from "Manual" Sheet the number from colume G2 and multiply it by the percentage in sheet "AllocationRule".

My formula currently is =Manual!$G$2*AllocationRule!$B5.

What needs to happen is that the total number in "Manual" needs to be distributed evenly in 4 rows by the percentages allocated in "AllocationRule".

Right now I can't copy my formula over to the sheet because the "AllocationRule" should stop at B5 and not go further and the G2 from "Manual" should not change for the percentage allocation but should change to the next row for the next month.

And then after I've done the calculation I want the LOB in "AllocationRule" to be displayed in the LOB in "H1913_H1914" but I'm not sure what formula to use.

I've attached my spreadsheet. Please help. Thank you.

[JBeaucaire]

Nasty formula...but put this in G2 and copy down and across:

=INDIRECT("Manual!"&ADDRESS((CEILING((ROW()-1)/4,1)+1),COLUMN()))*INDIRECT("AllocationRule!$B"&MOD(ROW()-1,4)+1)


EDIT:

My bad. Also, on the Allocation page, put the values in row 5 onto row 1 (eliminate the titles)

Please Login or Register  to view this content.

[yuzi]

Thank you that worked. Is there any way that you can explain your formula. Also, how do I get LOB in "AllocationRule" to be displayed in the LOB in "H1913_H1914" but I'm not sure what formula to use.

[JBeaucaire]

Actually, if you had just put in the LOB values by hand and just copied them down (it's just 4 values repeated over and over, right?), then the formula in G2 would have been simpler.

I would put in the first 4 values in B2:B5
BN
BI
BH
BC

Then in B6 put in the formula =B2 and copy that down to get all the values to appear, sets of four.

Now, the formula in G2 can change to:

=INDIRECT("Manual!"&ADDRESS((CEILING((ROW()-1)/4,1)+1),COLUMN())) * INDEX(AllocationRule!$B$2:$B$5,MATCH($B2,AllocationRule!$A$2:$A$5,0))


The red INDEX/MATCH section is a more standard method of doing this kind of thing.

If you want to watch the formula unfold and see how the pieces fit together, turn on the Formula Auditing toolbar, click on G2 and click on the Evaluate Formula. Evaluate it one step at a time and watch. Very informative.

[yuzi]

From what I understood from your post, I created my formula. However, it doesn't work. I've attached what my results are based on what I understood.
Thanks.

[JBeaucaire]

Hehe...nope. I meant put those repeating values on H1913 page.

[yuzi]

Oh ok thanks. But the problem is when I do that and enter the formula =AllocationRule!A2 and press enter it shows the formula not the value. Why is that?

[JBeaucaire]

Because you formatted the column as text. I reformatted the cell as GENERAL before putting in those first 4 formulas, then used =B2 in cell B5 and copied down.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].





(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)