How do I fill cells automatically, but only after the relevant cells are filled?

M

Matt Stephans

So row 1 is a filled row, say date, time, cost, avg cost, total cost.
What I want is for row two to be blank, but when I put in the cost on
row two, the avg costs and total costs automatically fill in. Right
now, one of the formulas does this automatically, but the other does
not. I can drag the formulas down manually, but doing that now
produces N/As and looks crappy. How do I make both formuals do this?
Thanks.
 
A

AlfD

Hi!

Can you post the data/formulae?

Meanwhile: if, for example, you put a cost in A2 and B2 & C2 sho
respectively some kind of total and average, you can tell B2 and C2 t
stay blank if A2 is blank by using

=if(A2="","",sum(...whatever))
& likewise average.


Al
 
M

Matt Stephans

Sure:

Date Buy-in Finish Payout Payout (Buy-in) Net
1-Oct $11 1 $39 3.55 $39
1-Oct $11 0 ($11) (1.00) $28
2-Oct $11 1 $39 3.55 $67
2-Oct $11 0 ($11) (1.00) $56
3-Oct $11 1 $39 3.55 $95

First 3 columns are entered, 4th is an if statement based on the 3rd
column. The 5th is the 4th divided by the 2nd, and the 6th is
SUM(D$2:Dx) where x is the row of the cell. Now the 7th row is blank,
but when I enter the first three columns of the 7th row, the 4th and
5th cells get filled automatically, but the 6th doesn't.

I know about the IF trick, but these cells are blank. I didn't even
know that there was a way for cells to pull a formula from a cell
above them, but this does. I can email the sheet if it would help.
Thanks for any assistance.
 
A

AlfD

Hi!

I looked at your sheet and I guess you have fallen foul of th
limitations on Tools > Options >Edit >"Extend data range formats an
formulas". Even with this checked, Excel will only automatically fil
in the formula if it satisfies some strict requirements. The followin
is from the MS Knowledge base:

*to determine whether it should adjust a formula, excel checks if th
following constraints are true: • you are using one of the followin
functions:

• average
• count
• counta
• max
• min
• product
• stdev
• stdevp
• subtotal
• sum
• var
• varp
-and-
• the formula contains a reference to only one cell or one range o
cells.
-and-
• you are not using absolute references.
-and-
• the new data that you are adding is consistent with data already t
the left or above the aggregate formula. *

Al
 
Top