MS Excel enter a formula into multiple cells conditionally

In the attached sample spreadsheet the percent column is blank in the customer totals row. What is the best method of  inserting a formula to calculate the percent rather than manually entering the formula into each cell  ? The formula for row 3 would be =G1/D1, row 5 =G5/D5, etc. The condition would be to enter the formula in column G for every row where the cell in column B is blank.The actual spreadsheet has over 500 rows. Thanks.
MiscSalesTest.xlsx
StampITAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
Apply Autofilter and select the empty cells in column H.
Make the formula for the first, =G3/D3
It creates an error because the subtotal is 0, due to the filter.
Copy down to the other cells.
Then remove the filter, and you have the result.
0
 
Ejgil HedegaardCommented:
Insert in H2 and copy down
=IF(B2="",G2/D2,"")
0
 
Dustin SaundersDirector of OperationsCommented:
I don't understand your explanation exactly.  Row 3 = G1/D1 but Row 5 = G5/D5?  Did you mean Row 3 = G3/D3?

If so, you can use the same formula in every row, as they are all the same calculation.  Starting with the first row then just drag that down the column.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
StampITAuthor Commented:
Yes. I meant Row 3 = G3/D3. The issue is I export from Access and the % is calculated in Access for the other cells. The only way I can calculate the customer totals and per cent-ages in Access is to create a report. My experience is Access reports do not export into Excel gracefully. I suppose I could simply calculate all the per cent-ages in Excel. However I would rather do it all in Access. Hope I'm not confusing the issue. thanks.
0
 
Dustin SaundersDirector of OperationsCommented:
So is this question about how to modify your report in Access to automatically include the percentages?  If so, please confirm and if you have SQL from your report that would be good.  And you may want to add the Access topic.

If that's not the case, then if the formula is the same for each row then rather than having it be conditional you can just blanket the whole column with the formula.  How are you exporting it from Access?  Is it that you are just exporting a table and those cells are from a computed column?
0
 
StampITAuthor Commented:
Dustin - I can structure an Access report to get all the data I need. However I have to present it in Excel to my
 users/clients. The issue is exporting the Access report to Excel exactly as it is in Access. I have not found this ever works very well. So I export the results of the Access query to Excel and format and calculate there. Thanks.
0
 
Dustin SaundersDirector of OperationsCommented:
You might consider opening an Access topic question on it, chances are theres a good way to do it without additional work in Excel.
0
 
StampITAuthor Commented:
I will. Thanks for the advice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.