Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 3rd 06, 01:11 AM posted to microsoft.public.excel.worksheet.functions
Trevor
 
Posts: n/a
Default Change over time fornula

Trying to calculate change % with a couple criteria. Here's what I'm doing...
Column a (labels) easy--data input
Column b (2004 number) easy--data input, all =0
Column c (2005 number) easy--data input, all =0
Column d (Change #) easy [=c2-b2] conditional format red font for neg#
Column e (Change %) here is the problem...I need to iclude all of these
conditions.
* if d2=0, then e2 should show "No Change" (or display 0%)...pretty easy so
far
* if there is a difference between b2 and c2, then change over time formula
is ((c2-b2)/b2). No problem s long as b2 is NOT zero
* trouble is, if b2=0 then I get a DIV error in the change over time
formula. I need any change from zero in 2004 to any number 0 in 2005 to
display "100%"

Would like some help creating a formula to care for this. Thank you.

  #2   Report Post  
Old January 3rd 06, 02:22 AM posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Change over time fornula

In e2: =if(b2=0,1,d2/b2). Format the result as a percent.

"Trevor" wrote:

Trying to calculate change % with a couple criteria. Here's what I'm doing...
Column a (labels) easy--data input
Column b (2004 number) easy--data input, all =0
Column c (2005 number) easy--data input, all =0
Column d (Change #) easy [=c2-b2] conditional format red font for neg#
Column e (Change %) here is the problem...I need to iclude all of these
conditions.
* if d2=0, then e2 should show "No Change" (or display 0%)...pretty easy so
far
* if there is a difference between b2 and c2, then change over time formula
is ((c2-b2)/b2). No problem s long as b2 is NOT zero
* trouble is, if b2=0 then I get a DIV error in the change over time
formula. I need any change from zero in 2004 to any number 0 in 2005 to
display "100%"

Would like some help creating a formula to care for this. Thank you.

  #3   Report Post  
Old January 3rd 06, 02:56 AM posted to microsoft.public.excel.worksheet.functions
Trevor
 
Posts: n/a
Default Change over time fornula

Disarmingly simple, it seems; works like a charm, thank you very much. I am
trying to understand what the formula does...(okay, besides the fact that it
works!)...may I ask you to break it down for me? It tells me that if b2 is
one or zero to divide the difference (d2) by the 2004 number (b2). Yes?

"bpeltzer" wrote: In e2: =if(b2=0,1,d2/b2). Format the result as a percent.

  #4   Report Post  
Old January 3rd 06, 03:03 AM posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Change over time fornula

The formula says that if b2 is 0, return 1 (100%) as the result. Otherwise,
the result is d2/b2. --Bruce

"Trevor" wrote:

Disarmingly simple, it seems; works like a charm, thank you very much. I am
trying to understand what the formula does...(okay, besides the fact that it
works!)...may I ask you to break it down for me? It tells me that if b2 is
one or zero to divide the difference (d2) by the 2004 number (b2). Yes?

"bpeltzer" wrote: In e2: =if(b2=0,1,d2/b2). Format the result as a percent.

  #5   Report Post  
Old January 4th 06, 10:05 PM posted to microsoft.public.excel.worksheet.functions
Trevor
 
Posts: n/a
Default Change over time fornula

Okay Bruce, on further review, there is a missing element he "* if d2=0,
then e2 should show "No Change" (or display 0%)." -- even if the numbers are
zero.

Using your suggested formula below, and considering the above criteria, when
the 2004 number is zero and the 2005 number is zero, cell e2 returns "100%"
when I was hoping for "0%" because a change from 0 to 0 is nil. Thoughts,
please?
Trevor

"Trevor" wrote:
snip<
Column e (Change %) here is the problem...I need to iclude all of these
conditions.
* if d2=0, then e2 should show "No Change" (or display 0%)...pretty easy so
far
* if there is a difference between b2 and c2, then change over time formula
is ((c2-b2)/b2). No problem s long as b2 is NOT zero
* trouble is, if b2=0 then I get a DIV error in the change over time
formula. I need any change from zero in 2004 to any number 0 in 2005 to
display "100%"



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
change date based on time kdp145 Excel Discussion (Misc queries) 7 December 14th 05 02:05 AM
Can series tool change formulas over time when used to copy them compu_trainer Excel Worksheet Functions 3 May 26th 05 07:55 PM
change format for time value Qaspec Excel Discussion (Misc queries) 1 May 23rd 05 10:25 PM
HOW CAN I CHANGE SET-UP ON MULTIPLE SHEETS AT 1 TIME? DEMONET48 Setting up and Configuration of Excel 0 January 14th 05 07:53 PM
Calculate change over time Gwen Excel Worksheet Functions 0 January 1st 05 05:52 PM


All times are GMT +1. The time now is 12:15 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017