# Problem with cell calculation formula.

#### Iron_Wolf

##### New Member
Using Office 2000 for this application with windows XP.

Column A has numbers (REVENUE) in it but not all cells, as this column is for cost of doing a two or three day trip. For an example I will use 10 rows.

A1 = 200, A3 = 300, A6 = 130 and A8 = 200.

Column B has numbers (KMS) in it consecutivly but not all the way to B10 as this column has kms driven each day.

B1= 350, B2 = 320, B3 = 230, B4 = 500, B5 = 340, B6 = 450, B7 = 300, B8 = 380, B9 = 250 and B10 is blank.

Column C needs to calculate the total of A to its current row and divide it by column B total to its current row. A1 and B1 being constant. If nothing is entered into B then C of that row should be blank.

Using the above example the cell contents of C should be as follows.

C1 = .57, C2= .30, C3 = .50, C4 = .33, C5 = .27, C6 = .28, C7 = .24, C8 = .28, C9 = .26 and C10 should be blank.

The formula I am using, or should I say thought would work, in column C is as follows. Cell C10

=IF(B1>0,(\$A\$1:A10)/(\$B\$1:B10),"")

Using that formula, copy and pasted the formula in all olumn C cells,I get the following,

C1 = .57, C2 = 0, C3 = 1.30, C4 = 0, C5 = 0, C6 = .29, C7 = 0, C8 = .52, C9 = 0 and C10 is blank.

Hoping I am just having a brain fart and missing something but thought it was an easy formula. If something is not entered in the B column I don't want it to do anything, no error no nothing just be blank. If there is a number in b column I want it to add up A column to current row and divide it by the total of column B to current row.

I know this is an easy fix but I can't figure it out and it's driving me nuts.

Hope you all can help. Thanks to all that respond.

Iron_Wolf

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Andrew Poulsom

##### MrExcel MVP
In C1 enter:

=IF(B1,SUM(A\$1:A1)/SUM(B\$1:B1),"")

and copy down.

#### Iron_Wolf

##### New Member
That works but on my original I am now getting a division error on my first few cells because I don't have naything entered in revenue for first cell. Guess I should have blanked the first row in my example.

I use 2007 and can't seem to remember 2000 rules LOL.

Thanks for the help Andrew, could you write the smae formula so that errors show up blank as blank cells.

Iron_Wolf

#### Iron_Wolf

##### New Member
Sorry, had wrong values calculating into each other. Not getting the #DIV Error.

I am however getting a #VALUE! error in cells where no other data is entered in other columns. Tried wirting a ISERROR formula with your formula but can't get the proper formula.

Why couldn't the formula's be compatable in all versions, wiat that would be to easy.

Any help would be appreciated.

#### Andrew Poulsom

##### MrExcel MVP
With the data you posted my formula will work in all versions of Excel.

#### Iron_Wolf

##### New Member
Andrew,

I tried a few things in my original sheet and found out some interesting things. If I delete the formula that is in the cell I am using to start the IF I do get a blank cell.

So it is my fault in not discribing the formula I am trying to get.

Using my above example again, Column B is not an entered amount it is a formula calculation entry. The formula that is in that cell on my sheet is

=IF(D10,(D10-D9)*0.621371192,"")

If you put that formula into B10 you will see that there is now a VALUE error showing in C10.

Without deleting the formula in B10 how do you get a blank cell in C10 keeping your fomula as well.

Sorry if I made a mistake in earlier post.

Thanks
Iron_Wolf

Last edited:

#### Andrew Poulsom

##### MrExcel MVP
I see. Change the formula in C1 to:

=IF(B1="","",SUM(A\$1:A1)/SUM(B\$1:B1))

and copy down.

Replies
7
Views
826
Replies
5
Views
202
Replies
1
Views
670
Replies
3
Views
200
Replies
1
Views
320

### Forum statistics

1,190,633
Messages
5,982,040
Members
439,751
Latest member
sohamkhatri ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back