Link to home
Start Free TrialLog in
Avatar of StampIT
StampITFlag for United States of America

asked on

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
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Insert in H2 and copy down
=IF(B2="",G2/D2,"")
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.
Avatar of StampIT

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Avatar of StampIT

ASKER

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.
You might consider opening an Access topic question on it, chances are theres a good way to do it without additional work in Excel.
Avatar of StampIT

ASKER

I will. Thanks for the advice.