Apportioning revenue
 

macropod Wrote:
So what do you need help with? How to input the formula?

Cheers

Thanks for the reply. What I need is a formula/function that I can put
in the uppermost cell of the final answer column, and that I can then
copy through out the rest of the column, if you follow my drift.
Currently my spreadsheet lloks like this:
Product Item Product Volume Item Volume Product Rev
Item Rev
Product A xxx 10
Product A yyy 5
Product A zzz 25 10
1000

the formula I use is Item Volume x Product Revenue / Product Volume.
When I this formula into Item Rev column, I have to use absolute
referencing for the Product Volume and Rev rows, which is fine if there
were only the three rows. The formula then has to be corrected for the
next (and each subsequent) Product/item mix. I could use vlookup
tables to insert the product volume and rev on each row, I suppose?
But is there a better way of arriving at the answer for each different
mix?
Thanks


--
Chris Lane

Chris Lane Wrote:
Currently my spreadsheet lloks like this:
Product Item Product Volume Item Volume Product Rev
Item Rev
Product A xxx 10
Product A yyy 5
Product A zzz 25 10
1000


Hi Chris,

You data layout suggests row 1 is a header row, and that you need the
formula to calculate item revenues to go in Column F. I assume that,
below 'Product A' you'd have a listing for Product B, C etc, and that
one of the rows for each product records the Product Volume and Product
Revenue (columns C & E, respectively). In that case, the following
formula, entered into F2 and copied down as far as you need, will
apportion the revenue for each product:
=D2/SUMIF(A:A,A2,C:C)*SUMIF(A:A,A2,E:E)
Note that I haven't used any rounding, so the result for a given item
might not come out to an exact number number of dollars & cents. If you
need it, you can wrap a rounding function around this formula.

Cheers


--
macropod