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