Calculate Average

[pigsfoot]

Hi,

I hope someone can help, although i'm not even sure if this is possible.

I have a spreadsheet where i enter purchase and sale figures of products, the number of transactions differ every day, one day it maybe two lines others it could be 3 or 4 lines

Each line has three columns, buy, sell and % profit

I have a second worksheet that lists the totals on a daily basis, each row is a day which has three lines. Total Purchase Value, Total Sales and i would like the third to be average % profit.

What i cant work out is how to create a formula that works out the average % profit based on the individual % profits per transaction... bearing in mind the number of transactions not being fixed

I guess what i'm asking is, is there a way to work out total number of lines for a given day of the month. I can then total all the percentage's up and divide it by the number of transactions calculated first ??

I really hope that makes some sense, as i have said i'm not even sure if this is possible.

Thanks

 

[estevaoba]

Assuming profit values are in range C2:C10, try this:

[TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]=AVERAGE(OFFSET(C$2,,,COUNTA(C$2:C$10),1))[/TD]
[/TR]
</tbody>[/TABLE]

Godspeed!

 

[igold]

In my opinion, at the end of the day, week, month, etc., there is not a useful average profit margin that means anything by the number of transactions. Your margin is your total profit (Sales minus Cost) divided by your Sales. The number of transactions that comprise these totals is meaningless. Yes it is good to know what items are generating what kind of margins, but in the aggregate, the number of transactions is not useful.

For example: if your cost on three items is $10/each and you sell two of them for $14/each (28.57% margin) and sell the last one for $40 (75.00% margin), your profit margin for the three sales is 55.88%. If you tried to add up the individual margins for the three items and then divided by three you would get a 44.05% average margin, which is a misleading number.

Just my two cents.