• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to simplify the excel formula for average cost

enterenter

New Member
Hi,

I did a excel to find a average cost. It need to calculate the row by row.

I have 2 questions

Q1) Is there any formulae or shortcuts (without column H, I, J) to get the final average cost 57.47 directly?

Q2) Is there any formulae or shortcuts (without column H, I, J) to get the average cost for a pointed row ?
or Can I input a transaction number to check the average price after finished that transaction.

e.g. After finished 8 transactions, I input transaction 3 to excel, it show 79.48

Thanks a lot!

75520
 

Attachments

  • Sample average cost 1.xlsx
    16.4 KB · Views: 4
Is there any formulae or shortcuts (without column H, I, J) to get the final average cost 57.47 directly?
What do you mean? Results seem correct?
In index/match combination will retrieve whet you need
If you plan to add more lines change your existing range to a Excel Table and rebuild the formula
 

Attachments

  • Copy of Sample average cost 1.xlsx
    10.5 KB · Views: 4
Hi pecoflyer,

Thank you very much for your message.

If index/match combination is used, I have to work out column H, I, J.
Is there way to use 1 formula to calculate the each avg.price without to use data on column H,I,J ?

I would like to simplify the table like A17:H25. But I don't how to setup the formula for H18:H25.

Thanks for remind to use excel table for maintain the data in long term.

Have a nice day!

75544
 

Attachments

  • Sample average cost 2.xlsx
    16.9 KB · Views: 6
Everything can be combined in one more or less complicated formula.
And the more complicated, it gets , the more difficult it is to find errors when they occur
OTOH leaving as is is much clearer, especially if someone else works on the sheet or when you come back to the sheet after not having used it for some time (hiding intermediary columns is possible)
 
Hi pecoflyer,

I guess you should a experience supervisor.
Yes, if the excel table has intermediary/helper columns, it is more easy to understand the logic and trace the problem, especially after 1 or few years.

However, after I did this excel table, I would like to make it more clear and simple presentation. Hope that this process can help to improve my excel skills. If I satisfy with current solution, it is difficult to go next level or always stay at same place.

Whatever the solution is more/less complicated. I may find what I may overlooked.

Anyway, thank you very much for your valuable advise. :)

Have a nice day!
 
Back
Top