# 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
###### Who is Participating?

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.

Commented:
Insert in H2 and copy down
=IF(B2="",G2/D2,"")
0
Director 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
Author 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
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

Experts Exchange Solution brought to you by

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

Director of OperationsCommented:

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
Author 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
Director 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
Author 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.