Division formual 1

[Cpreston]

Hi

This is a basic question but for some reason I cannot get the formula correct

I want to divide all figures in B column by one figure in Column reference I12 and display the figures in F column

If it do =sum(b2/I12) I get the result in F2 correct.
If I then copy this to into F3 it puts the formula to =SUM(C3/I13) and not keeping it at I12

I want it to always keep it at I12 and be able to populate the sum down the F column (without copy and paste) where the sum is possible from the B column.

Any ideas how I can do this please

Thanks

 

[1726]

Use:

=B2/$I$12 in F2

then copy down column.

the Sum function is not required for simple arithmetic.

The $ symbols create an Absolute cell reference which is not changed when the formula is copied/filled to other cells.

cheers

 

[Cpreston]

Hi

Thanks, yes that works ok, shame there is no way to auto populate the entire F Colum while the B column is true

Thanks

 

[1726]

A more advanced formula which will detect if the division is possible or not would be:

=IF(ISERROR(B2/$I$12),"",B2/$I$12)

This detects if the division creates an error, if it does it leaves the cell blank, if not it performs the calculation.

You could replace "" with a zero, or enter an error message of your choice between the quotes.

e.g.
=IF(ISERROR(B2/$I$12),0,B2/$I$12)
=IF(ISERROR(B2/$I$12),"Invalid Data",B2/$I$12)

The above formula would detect any type of error and so is suitable for a variety of situations.
Division will only cause an error when dividing by zero (or blank cell) (assuming only numeric data!)
so you could use a variation such as:

=IF($I$12=0,"",B2/$I$12)

 

[SkipVought]

Hi,

What do you mean by, "while the B column is TRUE?"

if you have Excel version 2007+ then
[tt]
=IFERROR(B2/$I$12,0)
[/tt]