Problem with cell calculation formula.

Iron_Wolf

New Member
Joined
Apr 9, 2008
Messages
14
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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.
 
Upvote 0
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.

Appreciate your help.

Thanks
Iron_Wolf
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top