Average Question

[Guest]

I have created an inventory sheet and am wanting to average the cost of the 4
deliveries, excludung empty if i did not recieve an order that week. I have
it set up like this del1 amount del2 amount del3 amount del4 amount. If I
try and use the suggestion that help gives me it gives me all of the cells as
an average, I dont need what amount i got delivered, just the prices
averaged. Can someone give me a clue? Thanks
FRM

 

[JulieD]

Hi

what cells are the delivery costs in?
if they're in C2,E2,G2,I2
then the formula to average the cost would be
=AVERAGE(C2,E2,G2,I2)

Cheers
JulieD

 

[Bob Phillips]

If you average like this

=AVERAGE(B18,D18,F18,H18)

if any are zero, it won't be included in the average.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

But the situation, it still divides by 5 no matter what. If there is say 1 of
the cells with a zero in it then it, then it should divide by4, for instance
if the 5 cells are as follows with amounts. 5.25,5.65,0,5.45,0. with this
formula the average would be3.27 which is incorrect. the average should be
5.45. Thats where I am having the difficulties, any suggestions?
FRM

 

[Bob Phillips]

If you can force a blank in there rather than 0, it won't average. Average
will include 0, as it is a numeric value, so why wouldn't it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Yes, I know and that would work fine other than, in the next cell after the
average is done I have an actual cost of all deliveries x number of goods
brought in. If I do not put a 0 in the empty cells, then it does not
calculate and instead gives me an error, here is the formula for the average,
and the formula for the actual.
Avg =AVERAGE(F5,H5,J5,L5,N5)
Actual =SUM(G5*H5)+(I5*J5)+(K5*L5)+(M5*N5)+(E5*F5)
The reason i want to average these intakes is so that I can at the end of
the month do an avg cost on my inventory as weekly prices change and it would
be to extensive to build something to track all that, yet alone sit here and
input it all lol.
But I need the actual as well to figure in my food cost and such monthly and
weekly. I am stuck as to what to do with it.
FRM

 

[Bob Phillips]

You are going to have a real job on trying to average over non-contiguous
cells ignoring zero, whereas ignoring blank is easy, the AVERAGE formula
works.

Do you mean that this formula errors
Actual =SUM(G5*H5)+(I5*J5)+(K5*L5)+(M5*N5)+(E5*F5)
when one or more of F5,H5,J5,L5,N5 is blank, because I just tried it and it
works fine for me with blank.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Oh I forgot to add that many times a re-design is much better than trying to
plough on with code and development.

--

HTH

RP
(remove nothere from the email address if mailing direct)