excel help - calculating the percent in subtotals only (sounds, work, click)

[foma]

For each client, I have two colums: Total Sales and Total Returns.

Each client has multiple transactions so I subtotaled (using the subtotal function in Excel) the "Total Sales" and "Total Returns" column.


Now I need to find the percent of Total Returns to Total Sales. How can I do this on the subtotal only without doing it for each transaction? I tried dragging the formula down, but it does the calculation for each transaction when I want it for the subtotals only. There's a LOT of customers so I'm trying to find an easier way than having to manually delete all the percentages from each transaction and keeping only the subtotal percentage.

If anybody has an idea - I'd appreciate it!!

[Bo]

It sounds like a reference issue. When you move the formula down, it's doing each calculation based on the relative position of the source cells, when you want it to stay locked on the subtotal cell. To fix that, you need to change the reference in the formula to the subtotal cell from relative to absolute. The Excel Help feature can advise you on setting references in your formula.

[foma]

i will look the reference thing up - thanks!!

[zman0]

Originally Posted by foma

How can I do this on the subtotal only without doing it for each transaction? I tried dragging the formula down, but it does the calculation for each transaction when I want it for the subtotals only.

I tried something like this, so hopefully it works for you.

Make sure you have a header row (e.g. "Customer Name" "Total Sales" "Total Returns").

Select the header row and click Data->Filter->AutoFilter. You should now have a pull down menu in each of the header cells.

Click the "Customer Name" menu, and select "Custom..."

You want to Show rows where Customer Name Contains total
- in the first pull-down menu select "contains"
- in the first text box, type "Total"

This should give you a worksheet which only shows the subtotals (which will likely all be $0.00).

Now you can insert your formula (which will show up as #DIV/0!) and drag it down to each of the subtotals, without getting the intervening transactions.

Select Data->Filter->Autofilter and you should be back to normal (without the #DIV/0!)

Hope this works for you.

[foma]

great idea with the filter ... thanks!!