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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ejgil HedegaardCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.