Using the IF Function effectively

  • Thread starter Thread starter neilangelo
  • Start date Start date
N

neilangelo

I have a spreadsheet with 4 columns . I want to create a formula whereby
the last column divided by column 1 plus the last column divided by
column 2 plus the last column divided by column 3 gives me a result.
However some of my columns may have zeros which may invalidate may
result and give me the "#DIV/O! error.

My spreadsheet has 100's of rows and I want to create a formula that I
can copy down ..instead of creating a formula line by line.

The formula needs to detect if the cell has zero in there , if it does
then it must give a 0 result , otherwise it should execute the function
and then continue to test the next columns for the same set of rules .


Even if one of the cells has a value then the total column divided by
that cell should be the overall result if the other cells are zero

In other words the formula should be able to eliminate cells with zero
and divide cells which have values.

please help. I need to solve this.
 
If I understand correctly try with the data in columns A:D
=IF(A1<>0,(D1/A1),0)+IF(B1<>0,(D1/B1),0)+IF(C1<>0,(D1/C1),0)
Regards,
neilangelo said:
I have a spreadsheet with 4 columns . I want to create a formula whereby
the last column divided by column 1 plus the last column divided by
column 2 plus the last column divided by column 3 gives me a result.
However some of my columns may have zeros which may invalidate may
result and give me the "#DIV/O! error.

My spreadsheet has 100's of rows and I want to create a formula that I
can copy down ..instead of creating a formula line by line.

The formula needs to detect if the cell has zero in there , if it does
then it must give a 0 result , otherwise it should execute the function
and then continue to test the next columns for the same set of rules .


Even if one of the cells has a value then the total column divided by
that cell should be the overall result if the other cells are zero

In other words the formula should be able to eliminate cells with zero
and divide cells which have values.

please help. I need to solve this.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
=IF(A1=0,0,D1/A1)+IF(B1=0,0,D1/B1)+IF(C1=0,0,D1/C1)

Vaya con Dios,
Chuck, CABGx3


neilangelo said:
I have a spreadsheet with 4 columns . I want to create a formula whereby
the last column divided by column 1 plus the last column divided by
column 2 plus the last column divided by column 3 gives me a result.
However some of my columns may have zeros which may invalidate may
result and give me the "#DIV/O! error.

My spreadsheet has 100's of rows and I want to create a formula that I
can copy down ..instead of creating a formula line by line.

The formula needs to detect if the cell has zero in there , if it does
then it must give a 0 result , otherwise it should execute the function
and then continue to test the next columns for the same set of rules .


Even if one of the cells has a value then the total column divided by
that cell should be the overall result if the other cells are zero

In other words the formula should be able to eliminate cells with zero
and divide cells which have values.

please help. I need to solve this.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top