calculating product sold and per unit profit

[4x4]

Trying to help my dad setup a sheet...Im not where I can access excel at the moment and thought someone might be able to help.


He is wanting to add a group of customers in the columns to keep track of gallons sold and on the rows column he wants profits to be able to calculate a cents per gallon and average gallon/profit to date for each customer.

Thanks in advance

[4x4]

What he is trying to do is capture how many gallons he is selling his customers and what his profit was in cents per gallon...and have a avg profit per gallon for YTD...so guess it would be broken into monthly totals and then have a YTD avg column. Maybe the YTD has to be on a second tab with each month being its own tab?

He sent me this workbook attached

[Bob Taylor]

Try this setup. I added a couple of necessary columns that he will want to see in his worksheet, i.e. cost per gal, profit per gal.

[Bobsone]

Hi 4x4
Have you sorted the sheet you are setting up?
If not, could you give a little more information, e.g. is the "per Gallon" purchase price and sale price constant, or does it fluctuate, are the customers known/return customers or walkins, how long do you want the sheet to run (days, weeks, months...) and how many transactions will be entered?

Regards

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[4x4]

Thank you Bob Taylor for the response, I like that...I will show it to my Dad.

Admin I adjusted title, hopefully that is ok

[4x4]

Bobsone, I am running the sheet that Bob Taylor posted above you by my dad..I will post any feedback I get..

Im not sure if he already has the monthly customer totals tallied into on transaction or if he wants to input each individual one.

I don't believe the per gallon purchase price and sales price are constant but I will double check on that...I think it fluctuates though.

Customers are known for the most part but I imagine he has new ones every once in a while.

I would think personally monthly totals is what he is looking for but he gave me such vague info Im not totally sure.

This is his email to me...as you can see not much info

I WANT TO ADD A GROUP OF CUSTOMERS ON A VERTICAL CELL FOE GALLONS AND ON THE
HORIZONTAL CELL HAVE PROFITS TO GET A CENTS PER GALLON AND AVERAGE GALLON/PROFIT
FOR CUSTOMER TO DATE.

[4x4]

Scan0001.jpgScan0002.jpg

Ok finally got back to looking at this, he gave me this copy of exactly what he is looking for. Thanks again for the help

[FDibbins]

OK, I dont know about any other members, but I am not going to even try and make sense of those pics.

Try and put that into a basic workbook and upload that please

[4x4]

Ok sorry here is what my Dad was trying to say with his hand written sheet... I put into excel format but I know there is probably a better way than I did it which was basic.

Thanks again

[FDibbins]

Not sure if I follow what your question is, but on sheet1 AM3, you could use this...
=SUMIF($B$2:$AK$2,$AI$2,B3:AK3)
and then for AN3...
=SUMIF($B$2:$AK$2,$AJ$2,B3:AK3)

If this is not what you wanted, please show me what it was you needed

[Bobsone]

Hi
How is the Net Profit value derived, is that calculation done somewhere else and then manually entered?

[4x4]

Yes sorry he apparently didn't need it to calculate the net profit, he is going to have it already calculated and simply input...looks like to me he will already know his customer totals and profit made per customer so there may not be any way to make this sheet any better than it is?