Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Nectar
 
Posts: n/a
Default Create an increase or decrease formula with zero %

PLease help i'm trying to set up my worksheet to calculate the increase or
decrease percentage from YTDr to LYTD

YTD= 200 LYTD=145 =.38% formula =(a1-b1)/abs(b1) but when I have a zero my
formula wont work please help me to create a formula that could work with
zeros

Thanks a lot my saviors !!!!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Create an increase or decrease formula with zero %

=IF(B1=0,"",(A1-B1)/ABS(B1))

Vaya con Dios,
Chuck, CABGx3



"Nectar" wrote:

PLease help i'm trying to set up my worksheet to calculate the increase or
decrease percentage from YTDr to LYTD

YTD= 200 LYTD=145 =.38% formula =(a1-b1)/abs(b1) but when I have a zero my
formula wont work please help me to create a formula that could work with
zeros

Thanks a lot my saviors !!!!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Nectar
 
Posts: n/a
Default Create an increase or decrease formula with zero %


"CLR" wrote:

=IF(B1=0,"",(A1-B1)/ABS(B1))

Vaya con Dios,
Chuck, CABGx3


HI Chuck Thanks a lot for your help
I'm getting a error, the formula work but went it a zero for LYTD nothing
comes up in my cell is in blank Any suggestions?
YTD 500 LYTD 0 =500% suppose to have 500%
"Nectar" wrote:

PLease help i'm trying to set up my worksheet to calculate the increase or
decrease percentage from YTDr to LYTD

YTD= 200 LYTD=145 =.38% formula =(a1-b1)/abs(b1) but when I have a zero my
formula wont work please help me to create a formula that could work with
zeros

Thanks a lot my saviors !!!!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Create an increase or decrease formula with zero %

The "blank" return in the case of a 0 in B1 is intentionally coded into the
formula with the "" part.....what would you like to have as a return when
B1=0?......if you want zero, then replace the "" with 0, as
=IF(B1=0,0,(A1-B1)/ABS(B1))
If you prefer some TEXT result, place it between the "", as
=IF(B1=0,"PutTextHere",(A1-B1)/ABS(B1))

hth
Vaya con Dios,
Chuck, CABGx3


"Nectar" wrote:


"CLR" wrote:

=IF(B1=0,"",(A1-B1)/ABS(B1))

Vaya con Dios,
Chuck, CABGx3


HI Chuck Thanks a lot for your help
I'm getting a error, the formula work but went it a zero for LYTD nothing
comes up in my cell is in blank Any suggestions?
YTD 500 LYTD 0 =500% suppose to have 500%
"Nectar" wrote:

PLease help i'm trying to set up my worksheet to calculate the increase or
decrease percentage from YTDr to LYTD

YTD= 200 LYTD=145 =.38% formula =(a1-b1)/abs(b1) but when I have a zero my
formula wont work please help me to create a formula that could work with
zeros

Thanks a lot my saviors !!!!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Nectar
 
Posts: n/a
Default Create an increase or decrease formula with zero %

If YTD I have 300 and LYTD 0 and I need to know the % of increase or decrease
the answer is or not? 30000% %CHANGE
Thanks for your help again


"CLR" wrote:

The "blank" return in the case of a 0 in B1 is intentionally coded into the
formula with the "" part.....what would you like to have as a return when
B1=0?......if you want zero, then replace the "" with 0, as
=IF(B1=0,0,(A1-B1)/ABS(B1))
If you prefer some TEXT result, place it between the "", as
=IF(B1=0,"PutTextHere",(A1-B1)/ABS(B1))

hth
Vaya con Dios,
Chuck, CABGx3


"Nectar" wrote:


"CLR" wrote:

=IF(B1=0,"",(A1-B1)/ABS(B1))

Vaya con Dios,
Chuck, CABGx3


HI Chuck Thanks a lot for your help
I'm getting a error, the formula work but went it a zero for LYTD nothing
comes up in my cell is in blank Any suggestions?
YTD 500 LYTD 0 =500% suppose to have 500%
"Nectar" wrote:

PLease help i'm trying to set up my worksheet to calculate the increase or
decrease percentage from YTDr to LYTD

YTD= 200 LYTD=145 =.38% formula =(a1-b1)/abs(b1) but when I have a zero my
formula wont work please help me to create a formula that could work with
zeros

Thanks a lot my saviors !!!!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Create an increase or decrease formula with zero %

Your example gives a 300 UNIT increase, not percent. There can be no
"percent increase" in the case where the starting point is zero.

Vaya con Dios,
Chuck, CABGx3


"Nectar" wrote in message
...
If YTD I have 300 and LYTD 0 and I need to know the % of increase or

decrease
the answer is or not? 30000% %CHANGE
Thanks for your help again


"CLR" wrote:

The "blank" return in the case of a 0 in B1 is intentionally coded into

the
formula with the "" part.....what would you like to have as a return

when
B1=0?......if you want zero, then replace the "" with 0, as
=IF(B1=0,0,(A1-B1)/ABS(B1))
If you prefer some TEXT result, place it between the "", as
=IF(B1=0,"PutTextHere",(A1-B1)/ABS(B1))

hth
Vaya con Dios,
Chuck, CABGx3


"Nectar" wrote:


"CLR" wrote:

=IF(B1=0,"",(A1-B1)/ABS(B1))

Vaya con Dios,
Chuck, CABGx3


HI Chuck Thanks a lot for your help
I'm getting a error, the formula work but went it a zero for LYTD

nothing
comes up in my cell is in blank Any suggestions?
YTD 500 LYTD 0 =500% suppose to have 500%
"Nectar" wrote:

PLease help i'm trying to set up my worksheet to calculate the

increase or
decrease percentage from YTDr to LYTD

YTD= 200 LYTD=145 =.38% formula =(a1-b1)/abs(b1) but when I have a

zero my
formula wont work please help me to create a formula that could

work with
zeros

Thanks a lot my saviors !!!!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel formula that calculates percentage of increase or decrease Ration Excel Worksheet Functions 2 January 23rd 06 05:10 PM
create pivot table formula without the GETPIVOTDATA function ebergkes Excel Discussion (Misc queries) 3 November 11th 05 01:25 AM
create reference formula that looks through all sheets for matchin BMW Excel Worksheet Functions 2 November 4th 05 05:20 PM
How do I create a 26 'tier' IF formula? callum Excel Discussion (Misc queries) 5 October 23rd 05 04:48 PM
How to create specific formula STS Excel Worksheet Functions 4 May 2nd 05 01:44 AM


All times are GMT +1. The time now is 10:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"