Average/sum with conditions...

[Painzstake]

[TABLE="class: grid, width: 805"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/11/2012[/TD]
[TD][/TD]
[TD]24/11/2012[/TD]
[TD][/TD]
[TD]17/03/2014[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Shipping Cost (USD)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 10[/TD]
[TD][/TD]
[TD] $ 10[/TD]
[TD][/TD]
[TD] $ 10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Discount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 5[/TD]
[TD][/TD]
[TD]$ 5[/TD]
[TD][/TD]
[TD] $ 5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Shipping-Discount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 'A'
[/TD]
[TD][/TD]
[TD] $ 5[/TD]
[TD][/TD]
[TD] $ 5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]USD total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 'B'
[/TD]
[TD][/TD]
[TD]$ 100[/TD]
[TD][/TD]
[TD] $ 100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AUD Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 105[/TD]
[TD][/TD]
[TD]$ 105[/TD]
[TD][/TD]
[TD] $ 105[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]#Bought[/TD]
[TD]Total[/TD]
[TD]Average[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Vitamin D[/TD]
[TD][/TD]
[TD]'C'
[/TD]
[TD]'D'
[/TD]
[TD]'E'
[/TD]
[TD]
[/TD]
[TD]'F'
[/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Magnesium[/TD]
[TD][/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[TD] $ 1.95[/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Vitamin K2[/TD]
[TD][/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1.95[/TD]
[TD][/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formulas in use:
Dark grey text is the current formula where applicable.

'A') =F2-F3

'B') =SUM(F9:F255)

'C') =COUNT(IF((MOD(COLUMN(F10:DA10)-COLUMN(F10),2)=0)*(F10:DA10),F10:DA10))
I would like to simplify this to a simple '=count($F9:$DA9)'

'D') {=SUM(IF((MOD(COLUMN(F11:DA11)-COLUMN(F11),2)=0)*(F11:DA11),F11:DA11))}
PHONETICALLY 'D'= f9+('A'/COUNT(F$9:F$254))+(('B'-F6)/COUNT(F$9:F$254)) <----- This formula spread across F9:DA9

'E') {=AVERAGE(IF((MOD(COLUMN(G11:IV11)-COLUMN(G11),2)=0)*(G11:IV11),G11:IV11))}
PHONETICALLY 'E'= Average(f9+('A'/COUNT(F$9:F$254))+(('B'-F6)/COUNT(F$9:F$254))) <----- This formula spread across F9:DA9. If there's also only one cell to average, I'd like it to average that eg. 1/25=25.

'F' =F10+(F$4/COUNT($F10:$F255))



Okay, I've tried semi-successfully to get a spreadsheet up and working for my orders of supplements. Up until this costing sheet it was smooth sailing... now I'm having trouble trying to work it out in an efficient manner. I've got a few conditions that I want to put into my formulas which now put the theory way over my head. But they are rather necessary because I keep getting nailed by conversion rates and shipping... So I'm trying to find out the average price of each product including it's part of any of the additional fees.

The columns are inputs for the orders dates, dollars items.
The rows are the supplements.

Column G is currently a hidden column for working out. I would like to hopefully remove it with a simplified equation.

Removing the #DIV/0!'s would be nice as well!

 

[C Moore]

I'm going to take some liberties on the interpretation (because it looks really complicated) and offer the following. [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]'C'
=counta($F9:$DA9)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]'D' =sum($F9:$DA9)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]'E' = 'D'/'C'
[/TD]
[/TR]
</tbody>[/TABLE]
If I'm not even close to what you're attempting, its because I don't understand the process.