Help with IF formula..its driving me mad!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following formula:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,(SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report 2003.xls]JAN:FEB'!$B$4)-1),(0))

This works fine if the division value (2003) is greater than 0, however if
the division value is 0 then I need the value from the 1st part of the SUM
range to display.

Can anyone help me pls?
 
Hi
do you mean:
=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4))
 
Hi,

I don't think this is what I mean, I can see what your saying in your
change. However what I'm after is probably another IF nested somewhere in my
original formula.

What I would like is for the formula to return the value as indicated, if
the logical test is true for the 1st IF, but then, if the value of the
division range

('[MPG(SIC) Report 2003.xls]JAN:FEB'!$B$4)

= 0 then return the value of the range

(SUM('[MPG(SIC) Report 2004.xls]JAN:FEB'!$B$4))

I hope this makes it a little clearer.

Regards
Frank Kabel said:
Hi
do you mean:
=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4))


MickJJ said:
I have the following formula:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,(SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report 2003.xls]JAN:FEB'!$B$4)-1),(0))

This works fine if the division value (2003) is greater than 0, however if
the division value is 0 then I need the value from the 1st part of the SUM
range to display.

Can anyone help me pls?
Click to expand...
Click to expand...
 
Hi
try:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,IF(SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)=0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4),
SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1),"not defined for <=0")

--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I don't think this is what I mean, I can see what your saying in your
change. However what I'm after is probably another IF nested
somewhere in my original formula.

What I would like is for the formula to return the value as
indicated, if the logical test is true for the 1st IF, but then, if
the value of the division range

('[MPG(SIC) Report 2003.xls]JAN:FEB'!$B$4)

= 0 then return the value of the range

(SUM('[MPG(SIC) Report 2004.xls]JAN:FEB'!$B$4))

I hope this makes it a little clearer.

Regards
Frank Kabel said:
Hi
do you mean:
=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4))


MickJJ said:
I have the following formula:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,(SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1),(0))

This works fine if the division value (2003) is greater than 0,
however if the division value is 0 then I need the value from the
1st part of the SUM range to display.

Can anyone help me pls?
Click to expand...
Click to expand...
Click to expand...
 
Hi,

OK I have tried your suggestion but I keep getting #REF returned in the
cell. I have traced the respective cells by using auditing and they do exist.
Any suggestions as to what I need to do now?

Regards

Frank Kabel said:
Hi
try:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,IF(SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)=0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4),
SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1),"not defined for <=0")

--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I don't think this is what I mean, I can see what your saying in your
change. However what I'm after is probably another IF nested
somewhere in my original formula.

What I would like is for the formula to return the value as
indicated, if the logical test is true for the 1st IF, but then, if
the value of the division range

('[MPG(SIC) Report 2003.xls]JAN:FEB'!$B$4)

= 0 then return the value of the range

(SUM('[MPG(SIC) Report 2004.xls]JAN:FEB'!$B$4))

I hope this makes it a little clearer.

Regards
Frank Kabel said:
Hi
do you mean:
=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4))


:

I have the following formula:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,(SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1),(0))

This works fine if the division value (2003) is greater than 0,
however if the division value is 0 then I need the value from the
1st part of the SUM range to display.

Can anyone help me pls?
Click to expand...
Click to expand...
Click to expand...
 
Hi
probably a missing space or too much. Really check if the cell
references are correct. Also check for linebreaks

--
Regards
Frank Kabel
Frankfurt, Germany

MickJJ said:
Hi,

OK I have tried your suggestion but I keep getting #REF returned in the
cell. I have traced the respective cells by using auditing and they do exist.
Any suggestions as to what I need to do now?

Regards

Frank Kabel said:
Hi
try:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,IF(SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)=0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4),
SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1),"not defined for <=0")

--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I don't think this is what I mean, I can see what your saying in your
change. However what I'm after is probably another IF nested
somewhere in my original formula.

What I would like is for the formula to return the value as
indicated, if the logical test is true for the 1st IF, but then, if
the value of the division range

('[MPG(SIC) Report 2003.xls]JAN:FEB'!$B$4)

= 0 then return the value of the range

(SUM('[MPG(SIC) Report 2004.xls]JAN:FEB'!$B$4))

I hope this makes it a little clearer.

Regards
:

Hi
do you mean:
=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1,SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4))


:

I have the following formula:

=IF('[MPG(SIC) Report 2004.xls]FEB'!$R$27>0,(SUM('[MPG(SIC) Report
2004.xls]JAN:FEB'!$B$4)/SUM('[MPG(SIC) Report
2003.xls]JAN:FEB'!$B$4)-1),(0))

This works fine if the division value (2003) is greater than 0,
however if the division value is 0 then I need the value from the
1st part of the SUM range to display.

Can anyone help me pls?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Formula 1
Excel formula, sum the value cell and if empty cell left blank 0
IF AND Nested Functions - Formula Not Working 0
IF Function 1
IF Function 1
Sumproduct not working 3
formula query - please help 2
Setting up "Progressive" Formulas between worksheets/files 1

Back
Top