formula

[chedd via OfficeKB.com]

Hi

I am using this formula to show in a report the difference in total
percentage between two months =IF(B9>'Apr 06 monthly report'!B9,"Higher",IF
(B9<'Apr 06 monthly report'!B9,"Lower","Equal")). This works fine until in
cell B and C are the same value i.e. 1 and 1. therefore in cell D it shows
100% creating cell E Higher value statement instead of equal. I have no
issues to when 0 is shown in cell B and C as this displayed as Equal. The
formula to show the percentage value is =IF(C9=0,"0%",C9/B9). Can anyone
help

 

[Guest]

=IF(B9>'Apr 06 monthly report'!B9,"Higher",IF(B9<'Apr 06 monthly
report'!B9,"Lower","Equal")).

This formula works fine, just as it's supposed to. The problem appears to
be in your smaller formula, whereas "0%" is actually TEXT, not the number 0%
and the two are not the same.....try this instead anf format the cell for
Percentage if desired.

=IF(C9=0,0,C9/B9)

Vaya con Dios,
Chuck, CABGx3

 

[chedd via OfficeKB.com]

Hi

I am using this formula to show in a report the difference in total
percentage between two months =IF(B9>'Apr 06 monthly report'!B9,"Higher",IF
(B9<'Apr 06 monthly report'!B9,"Lower","Equal")). This works fine until in
cell B and C are the same value i.e. 1 and 1. therefore in cell D it shows
100% creating cell E Higher value statement instead of equal. I have no
issues to when 0 is shown in cell B and C as this displayed as Equal. The
formula to show the percentage value is =IF(C9=0,"0%",C9/B9). Can anyone
help

I have tried to respond to the reply I had, but have been blocked to give a
reply. I have tried the formula below, but I still having 100% showing in
cell D. Has any one have any further ideas?

 

[Guest]

Please post the three formulas you are using in cells B, C, and D.

Vaya con Dios,
Chuck, CABGx3

 

[chedd via OfficeKB.com]

Hi

[quoted text clipped - 6 lines]

formula to show the percentage value is =IF(C9=0,"0%",C9/B9). Can anyone
help

I have tried to respond to the reply I had, but have been blocked to give a
reply. I have tried the formula below, but I still having 100% showing in
cell D. Has any one have any further ideas?

The formula in B is ='May 06'!F11, Cell C=B10-'Apr 06 monthly report'!B10,
Cell D =IF(C11=0,"0%",C11/B11) and in Cell E =IF(B11>'Apr 06 monthly report'!
B11,"Higher",IF(B11<'Apr 06 monthly report'!B11,"Lower","Equal")).

Hope this ok

Thanks

 

[Guest]

All appears to be in order, except possibly the usage of B10 in your formula
for cell C.........perhaps it should be

=B11-'Apr 06 monthly report'!B11

instead of

=B10-'Apr 06 monthly report'!B10

hth
Vaya con Dios,
Chuck, CABGx3

Hi

[quoted text clipped - 6 lines]

formula to show the percentage value is =IF(C9=0,"0%",C9/B9). Can anyone
help

I have tried to respond to the reply I had, but have been blocked to give a
reply. I have tried the formula below, but I still having 100% showing in
cell D. Has any one have any further ideas?

The formula in B is ='May 06'!F11, Cell C=B10-'Apr 06 monthly report'!B10,
Cell D =IF(C11=0,"0%",C11/B11) and in Cell E =IF(B11>'Apr 06 monthly report'!
B11,"Higher",IF(B11<'Apr 06 monthly report'!B11,"Lower","Equal")).

Hope this ok

Thanks

 

[chedd via OfficeKB.com]

Hi

[quoted text clipped - 6 lines]

formula to show the percentage value is =IF(C9=0,"0%",C9/B9). Can anyone
help

I have tried to respond to the reply I had, but have been blocked to give a
reply. I have tried the formula below, but I still having 100% showing in
cell D. Has any one have any further ideas?

Sorry i took the formula from B10 instead B11 (=B11-'Apr 06 monthly report'!
B11)

 

[Guest]

Ok then.....under those circumstances my sheet is working fine, returning 0%
in the case where both are equal.........are you still getting the 100% , or
has the problem gone away..........

Vaya con Dios,
Chuck, CABGx3

Hi

[quoted text clipped - 6 lines]

formula to show the percentage value is =IF(C9=0,"0%",C9/B9). Can anyone
help

I have tried to respond to the reply I had, but have been blocked to give a
reply. I have tried the formula below, but I still having 100% showing in
cell D. Has any one have any further ideas?

Sorry i took the formula from B10 instead B11 (=B11-'Apr 06 monthly report'!
B11)

 

[chedd via OfficeKB.com]

[quoted text clipped - 5 lines]

reply. I have tried the formula below, but I still having 100% showing in
cell D. Has any one have any further ideas?

Sorry i took the formula from B10 instead B11 (=B11-'Apr 06 monthly report'!
B11)

Where it is registered 0 in cell B i am getting 0% in cell D, but when the
cells B and C register above 0 each i.e 1 and 1 i am getting 100% with cell
E reading high instead of Equal.

 

[Guest]

The formulas you gave me appear to be working exactly as they are supposed
to. If you wish the results to be something different, then we will have to
change some things.

If you want cell D to be 0% when both cells B and C are equal, then change
the formula in Cell D to be...

=IF(OR(C11=0,C11=B11),"0%",C11/B11)

And, if you want cell E to read "Equal" when both cells B and C are equal,
then change the formula in cell E to be...

=IF(B11=C11,"Equal",IF(B11>'Apr 06 monthly report'!B11,"Higher",IF(B11<'Apr
06 monthly report'!B11,"Lower","")))


hth
Vaya con Dios,
Chuck, CABGx3

Hi

[quoted text clipped - 5 lines]

reply. I have tried the formula below, but I still having 100% showing in
cell D. Has any one have any further ideas?

Sorry i took the formula from B10 instead B11 (=B11-'Apr 06 monthly report'!
B11)

Where it is registered 0 in cell B i am getting 0% in cell D, but when the
cells B and C register above 0 each i.e 1 and 1 i am getting 100% with cell
E reading high instead of Equal.

 

[chedd via OfficeKB.com]

[quoted text clipped - 4 lines]

Sorry i took the formula from B10 instead B11 (=B11-'Apr 06 monthly report'!
B11)

Where it is registered 0 in cell B i am getting 0% in cell D, but when the
cells B and C register above 0 each i.e 1 and 1 i am getting 100% with cell
E reading high instead of Equal.

Thank you it worked atreat. However where there is a minus in cell c(-17)
t this shows in cell D as -65% giving a default in cell E #Ref!. Is there
any way in the formula in cell E to recognise the minus fig to show lower.

Sorry about this, but your help has been most valulable.

 

[Guest]

The formulas appear to work fine in my model with -17 in cell C. If you are
getting #REF! in cell E, then it is probably because either the "Apr 06
monthly report" file is not in the default directory, or does not exist, or
the filename is mispelled either on the file or in the formula....

hth
Vaya con Dios,
Chuck, CABGx3

Hi

[quoted text clipped - 4 lines]

Sorry i took the formula from B10 instead B11 (=B11-'Apr 06 monthly report'!
B11)

Where it is registered 0 in cell B i am getting 0% in cell D, but when the
cells B and C register above 0 each i.e 1 and 1 i am getting 100% with cell
E reading high instead of Equal.

Thank you it worked atreat. However where there is a minus in cell c(-17)
t this shows in cell D as -65% giving a default in cell E #Ref!. Is there
any way in the formula in cell E to recognise the minus fig to show lower.

Sorry about this, but your help has been most valulable.

 

[chedd via OfficeKB.com]

[quoted text clipped - 5 lines]

cells B and C register above 0 each i.e 1 and 1 i am getting 100% with cell
E reading high instead of Equal.

Thank you it worked atreat. However where there is a minus in cell c(-17)
t this shows in cell D as -65% giving a default in cell E #Ref!. Is there
any way in the formula in cell E to recognise the minus fig to show lower.

Sorry about this, but your help has been most valulable.

Sorry about this, but have checked and there are no errors. The problem
appears to be when there is a minus sign (-17 in cell C and -68%) is causing
the error message. All other cells which do not show a minus are okay.

 

[Guest]

Not a problem, if it don't work, it don't work and we have to find out why.

I went back to my model and created a sheet called "Apr 06 monthly report",
and retyped the formula in cell E and it all worked fine with the -17 in cell
C. The problem really is between the SheetName and the SheetName in the
formula. Could look the same but just be an additional space, leading or
trailing or something, any way, I suggest you re-create the SheetName on the
tab, and hand re-type the formula in cell E, and see how it works..........

hth
Vaya con Dios,
Chuck, CABGx3

Hi

[quoted text clipped - 5 lines]

cells B and C register above 0 each i.e 1 and 1 i am getting 100% with cell
E reading high instead of Equal.

Thank you it worked atreat. However where there is a minus in cell c(-17)
t this shows in cell D as -65% giving a default in cell E #Ref!. Is there
any way in the formula in cell E to recognise the minus fig to show lower.

Sorry about this, but your help has been most valulable.

Sorry about this, but have checked and there are no errors. The problem
appears to be when there is a minus sign (-17 in cell C and -68%) is causing
the error message. All other cells which do not show a minus are okay.

 

[chedd via OfficeKB.com]

[quoted text clipped - 7 lines]

Sorry about this, but your help has been most valulable.

Sorry about this, but have checked and there are no errors. The problem
appears to be when there is a minus sign (-17 in cell C and -68%) is causing
the error message. All other cells which do not show a minus are okay.

Great it is now working. I would like to thank you for your time and
patience in helping to resolve this issue.

 

[Guest]

My pleasure.........glad it 's working for you, and thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3

Hi

[quoted text clipped - 7 lines]

Sorry about this, but your help has been most valulable.

Sorry about this, but have checked and there are no errors. The problem
appears to be when there is a minus sign (-17 in cell C and -68%) is causing
the error message. All other cells which do not show a minus are okay.

Great it is now working. I would like to thank you for your time and
patience in helping to resolve this issue.