PUZZLED - Daily Percentage Change and Cumulative

Dear All,

I was never any good with maths and this just puzzles me!

I have date starting from 100 down column B with the date in column A.

The last number is 117.50.

I make this an increase of 17.5%....however each day it may have moved
down 1% or up 2%.  When I summate all the daily percentage changes I
was expecting to see 17.5% however I can see around 20%.

The formulas are all correct but I really need to know how to work out
the daily percentage change AND the cumulative percentage change from
100.

I hope someone can assist me.

S
0
11/11/2008 2:51:08 PM
excel 39879 articles. 2 followers. Follow

7 Replies
453 Views

Similar Articles

[PageSpeed] 51

rounding?

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Stuart" <swilson2006@gmail.com> wrote in message 
news:9e895808-499d-4977-a739-e63fd0bcbcab@v16g2000prc.googlegroups.com...
> Dear All,
>
> I was never any good with maths and this just puzzles me!
>
> I have date starting from 100 down column B with the date in column A.
>
> The last number is 117.50.
>
> I make this an increase of 17.5%....however each day it may have moved
> down 1% or up 2%.  When I summate all the daily percentage changes I
> was expecting to see 17.5% however I can see around 20%.
>
> The formulas are all correct but I really need to know how to work out
> the daily percentage change AND the cumulative percentage change from
> 100.
>
> I hope someone can assist me.
>
> S 

0
dguillett1 (2487)
11/11/2008 3:11:03 PM
That's the way the math goes. You seem to be comparing apples & oranges. Try 
this:
In A1:A10 enter the numbers 100, 101,102,...110
In B2 enter =(A2-A1)/A1 and format as percent, to find the increase in one 
period;
Copy this down the column; you get 1.00%, 0.99%, ....0.92%
The ten percentages sum to 9.58% while the change over the entire period is 
(110-100)/100 = 10.00%
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Stuart" <swilson2006@gmail.com> wrote in message 
news:9e895808-499d-4977-a739-e63fd0bcbcab@v16g2000prc.googlegroups.com...
> Dear All,
>
> I was never any good with maths and this just puzzles me!
>
> I have date starting from 100 down column B with the date in column A.
>
> The last number is 117.50.
>
> I make this an increase of 17.5%....however each day it may have moved
> down 1% or up 2%.  When I summate all the daily percentage changes I
> was expecting to see 17.5% however I can see around 20%.
>
> The formulas are all correct but I really need to know how to work out
> the daily percentage change AND the cumulative percentage change from
> 100.
>
> I hope someone can assist me.
>
> S 


0
bliengme5824 (3040)
11/11/2008 3:41:03 PM
Hi,

I think what you are asking is why the cumuliative percent change does not 
equal the sum of the individual percent changes?

Example:

Suppose you buy a $100 shirt which says 50% off - the cost would be $50.  
But suppose there was a sign that said for today only take an additional 50% 
off - what would your cost be?  50%+50%=100%? true but not the correct 
answer.  The second 50% is applied against the $50 cost or 50%*$50 = $25.  
The shirt will cost $25%.  What was your total saving $75 or 75/100 = 75%

-- 
Thanks,
Shane Devenshire


"Stuart" wrote:

> Dear All,

> I was never any good with maths and this just puzzles me!
> 
> I have date starting from 100 down column B with the date in column A.
> 
> The last number is 117.50.
> 
> I make this an increase of 17.5%....however each day it may have moved
> down 1% or up 2%.  When I summate all the daily percentage changes I
> was expecting to see 17.5% however I can see around 20%.
> 
> The formulas are all correct but I really need to know how to work out
> the daily percentage change AND the cumulative percentage change from
> 100.
> 
> I hope someone can assist me.
> 
> S
> 
0
11/11/2008 3:53:01 PM
To track the cumulative percent change, add 100% to the daily percent 
change, then multiply (not sum).

      100  100%
      110 10% 110% 110%
      115 5% 105% 115%
      125 9% 109% 125%


so 109% * 115% = 125%. Subtract again 100% to get 25%


"Stuart" <swilson2006@gmail.com> schreef in bericht 
news:9e895808-499d-4977-a739-e63fd0bcbcab@v16g2000prc.googlegroups.com...
> Dear All,
>
> I was never any good with maths and this just puzzles me!
>
> I have date starting from 100 down column B with the date in column A.
>
> The last number is 117.50.
>
> I make this an increase of 17.5%....however each day it may have moved
> down 1% or up 2%.  When I summate all the daily percentage changes I
> was expecting to see 17.5% however I can see around 20%.
>
> The formulas are all correct but I really need to know how to work out
> the daily percentage change AND the cumulative percentage change from
> 100.
>
> I hope someone can assist me.
>
> S 


0
11/11/2008 10:39:44 PM
On Nov 11, 6:51=A0am, Stuart <swilson2...@gmail.com> wrote:
> I have date starting from 100 down column B with the date in column A.
> The last number is 117.50.
>
> I make this an increase of 17.5%....however each day it may have moved
> down 1% or up 2%. =A0When I summate all the daily percentage changes I
> was expecting to see 17.5% however I can see around 20%.

You cannot sum daily percentage changes and expect that to equal the
cumulative change.  Instead, you multiply the daily change.

Actually, you multiply 1 plus daily change, then subtract 1.  Consider
the following simple sequence:  100, 110, 90.  The interval changes
are +10% and -18.18%.  The cumulative change is (1+10%)*(1-18.18%) =3D
-10%.  (Of course, "-18.18%" is really 90/110-1.)

One way to do that is with the following array formula (commit with
ctrl-shift-Enter):

=3DPRODUCT(B2:B100 / B1:B99) - 1

where B1:B100 is the data (100, 110, and 90 in my example).

However, PRODUCT has poor numerical characteristics for large data.
That can be avoided by computing the sum of the log, then computing
the antilog.  Big words; easier to write (again, an array formula):

=3D10 ^ SUM(LOG(B2:B100 / B1:B99)) - 1

Of course, format the result as Percentage.

HTH.
0
joeu2004 (766)
11/11/2008 11:52:13 PM
PS....

On Nov 11, 3:52=A0pm, I wrote:
> However, PRODUCT has poor numerical characteristics for large data.
> That can be avoided by computing the sum of the log, then computing
> the antilog. =A0Big words; easier to write (again, an array formula):
> =3D10 ^ SUM(LOG(B2:B100 / B1:B99)) - 1

You can avoid the array formula by entering the following normal
format (commit with just Enter):

 =3D10 ^ SUMPRODUCT(LOG(B2:B100 / B1:B99)) - 1


0
joeu2004 (766)
11/11/2008 11:57:45 PM
Errata....

On Nov 11, 3:52=A0pm, I wrote:
> Actually, you multiply 1 plus daily change, then subtract 1. =A0Consider
> the following simple sequence: =A0100, 110, 90. =A0The interval changes
> are +10% and -18.18%. =A0The cumulative change is (1+10%)*(1-18.18%) =3D
> -10%. =A0(Of course, "-18.18%" is really 90/110-1.)

Obvious typo:  forgot to "then subtract 1".  Klunk!  The cumulative
change evaluation should read:  (1+10%)*(1-18.18%) - 1 =3D -10%.
0
joeu2004 (766)
11/12/2008 12:01:09 AM
Reply:

Similar Artilces:

Embedded word doc changed to image-need to change back to word.
I have an embedded word document that misteriously changed into an image. This image is not a word doc converted into a picture (i.e. like I convert an embedded word doc by right clicking and selecting 'image object', 'covert', and then select to activate as a word doc.) but an actual image as if I inserted a picture. Need to turn the image back into a word doc so I can recover my work. It would also be nice to figure out how I am changing to an image, amost seems like a bug since it has randomly happened twice. ...

Effect of Domain change on Outlook
My PC was a stand alone PC that previously belonged to a domain (that was no more in use). I had Outlook running on it that could get my e-mails from several web e-mail services using POP3 (no Exchange). I had a few thousand e-mails in this outlook. For some reason I removed my PC from the Domain and join a workgroup. Now when I launch my Outlook, it wants to start from scratch. Can my previous e-mail accounts, rules, contacts, and vital e-mails be re-stored? Thank you in advance, Ben If you were using a .pst file for your mail storage, it may have a = different name than outlook.pst -...

Stop mail from changing postition once replied to
How can I stop Outlook 2003 e-mail message from changing location in my in box once I have addressed them or replied to them? It scoots them to the bottom of my e-mail list and just rotates them as I work them... sounds like you are sorting by the modified date or icon - click on the received field to sort by it. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange So...

Powerpoint 2007 Excel import change tab
Hi, The situation is as follows: Powerpoint template needs to import data from specific fields from within a tab in excel. This tab name changes depending on the client. Using the "paste Special" i can get the info imported fine. The part i have a problem with is changing the name of the tab to import different data.Powerpoint doesn't have an option under "Prepare>Edit Links To file" If anyone can suggest a solution even a macro to prompt the user to enter the name of the new sheet they want to pull the data from. The Execl file does not change ...

Outlook Client: Change the On Line Server address
Hi, Is it possible to change the address of the CRM server that the Outlook Client uses to syncronise? -- Thanks in advance Chris Hi Chris, You can make this change by editing the following registry key - HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient\ServerUrl. The usual warning - please be careful while making registry edits! HTH, Niths "BoltonWolf" wrote: > Hi, > > Is it possible to change the address of the CRM server that the Outlook > Client uses to syncronise? > -- > Thanks in advance > Chris Thanks for that, I think that works ok. I'...

Publisher 2007: How do I change dates on calendar page in newslet
I'm creating a newsletter. I added a calendar page. In previous versions of Publisher, I was able to get to Calendar Options to change the month. In Publisher 2007 when I insert a calendar page into a newsletter, I can no longer access the Calendar Options task pane. Please describe how to change the dates. The calendar wizard is broken. Hopefully it will be fixed in a future service pack. Meanwhile you will have to manually change the calendar dates. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Stephanie" &...

get/change first operation on table 'uprEmployeeCount' failed
I created a 'test' company on V10, restored the backup from our real company into this test company. I have inactivated all the employees in the test company. Unless I enter Dynamics as sa, I get the following message: A get/change first operation on table 'uprEmployeeCount' failed accessing SQL Data. Under the more info button: [Microsoft][ODBC SQL Server Drive][SQL Server] The EXECUTE permission was denied on the object 'zDP_UPR41600F_1',database 'DYNAMICS', schemo 'dbo'. I have deleted the UPR41600 table and recreated it but I still get the...

Durations keep changing... Help!
Ok... it's me again - brand new user... I need to list resources (sometimes multiple) for each task, but ONLY show it shows up on a report for each person. We do not assign or track real/actaul hours. So here's what I've done to try to simplify... Task #1: Tasks/Advanced Tab: "Must Start On" with a start date (this is the only constraint I've set in the project); Fixed Duration; Effort Driven unchecked All other tasks have the same settings except no constraint date and default to "As Soon as Possible." Each Resource: Booking type - P...

Lost ability in MSWord to make ANY changes to ANY document
Somehow when I open a document in Word, it will not let me do anything other than open the document, print and close, everything else come up 'faded'- I can't even make changes anymore, and i've tried restoring my system to an earlier date, but nothing- Version of Word? If it's 2007 and someplace on the screen it says something about "This modification is not allowed because selection is locked", it could be that you've been using a trial version that's now expired or you haven't properly activated your full version. -- Cheers! Gor...

Help with a formmula change in a "flag" parameter!
The formula I have below in Column R works just fine. It creates a flag for me (in Column R) that lets me know one of two things: “Client Failed to Enroll on Time”, and “Return Referral Form to Referent”. Here is that formula: =IF(H4="","",IF(K4<>"","",IF(S4="Sent","Client Failed to Enroll on Time",IF(AND(K4="",TODAY()>WORKDAY(H4,10,AA4:AA12)),"Return Referral Form to Referent","")))) The one change (addition) that I’d like to make, is this: If Column N is toggled to say “Hudson ...

P2002 S-L-O-W on Page Change
Recently "upgraded" to Publisher 2002 from Publisher 2000. A three-page document now requires about 45 seconds to switch from one page to another. One of the pages has about 12 text frames and 3 photos. I copied the file and removed all frames. Same delay. (And this file, that now has no visible content, has a size of 2.2MB.) This behavior is identical on two machines (removed 2002 from one machine and installed on another.) First machine is W2K, 600MHz, 256MB. Second is XP, 2.3GHz, 512MHz. Is this video related? Both machines have 64MB display cards. All Office, OS,...

Highlight changes within cell
Good morning! Using Excel 2003 I need to highlight the changes I’m making within a cell. For example: In cell B2, is the customer’s original order quantity of 200. I revise it to show 225 and I’d like the cell to be highlighted in yellow. I can then copy and paste the info into an email to show the customer which items have been revised. I’ve tried using Track Changes, but it seems that I have to click on the Track Changes button each time I open the workbook. It also doesn’t keep the revision highlighted for a copy and paste. I have 20+ worksheets within the workbook an...

Formula "Change" Problem
Situation: In cell A1, I have a simple formula that sums the data in the following 6 horizontal cells. The formula is =SUM(B1:G1) I want this formula to never change under any circumstance. The problem is if I insert a column before in front of column B the formula changes to the following: =SUM(C1:H1) The spreadsheet is setup so that it is supposed to calculate a rolling 6 week total. Each new week a new column will be inserted and the data for the new week entered. How can i keep the formula from changing as the worksheet changes? Thank you in adnvance to anyone that may be able to h...

How to reopen and make changes to a closed contract
I have been reading the blog from ms dynamics team about opening and changing a closed appointment: http://blogs.msdn.com/crm/archive/2007/03/08/how-to-reopen-a-closed-appointment-and-make-changes-to-the-file.aspx I'm trying to do the same thing with a contract. the code I used is almost the same. I changed "Appointment" with "Contract" and the state and state Reason: state code : ' <ContractState>Active</ContractState>'+ state Reason: ' <ContractStatus>3</ContractStatus>'+ see also http://msdn2.microsoft.com/en-us/library/a...

Change Directory Creation/Modification Date/Time programatically
Hi, Can anyone help me out to change the directory creation/modification date/time through C++/MFC code? Can this be acheived without changing the system time? Thanks in advance. Vignesh. In article <98EDAE87-3F31-4F06-AE8C-378CF91A6F3A@microsoft.com>, Vignesh@discussions.microsoft.com says... > Hi, > Can anyone help me out to change the directory creation/modification > date/time through C++/MFC code? SetFileTime. > Can this be acheived without changing the system time? Yes. -- Later, Jerry. The universe is a figment of its own imagination. "Je...

Puzzled by HPPT post Request Headers not arriving
Problem with HTTP Request Headers I have an app that uses an HTTP Post to send some stuff to a server and get some data back. It works OK but I would like to save bandwidth by compressing the return data. My problem is that the header that I want to send, to allow compression on the server, is not being received by the server. I have patched the serverscript to reflect back the headers and neither of the header strings seems to have got there. The post string itself gets there, but the 'Accept-Encoding' header does not. Please, what am I doing wrong? The code that tries...

COLOUR CHANGE IS A CELL
Cyber taz What I am trying to do is use say a red coulured cell but when text is added to the cell it changes colour to green. The other thing if possible a drop down menu in a cell so when clicked on it offers three of four selection For example today tomorow yesterday day after And once one wes highlighted it would show in the cell. Thankls for your help it is really apprereciated. You can use data validation to create a dropdown list in a cell. There are instructions in Excel's help, and here: http://www.contextures.com/xlDataVal01.html Mav wrote: > Cyber taz > What I am...

How to change OL 2007 storage location?
I would like to move my mail archive off drive C into another partition. Is there a way to do this and can folders be placed in separate files such that OL 2007 can use them as working files? Thanks, John. Yes, just move the .PST file and then change the account settings for the mailbox that points to it. But its all or nothing, all of the folders under the same email account have to point to the same .PST file, you can't have the INBOX and SENT in one .PST and the CALENDAR, CONTACTS and TASKS in another. You can have the .PST's for each email account in different ...

Marketing Member
Hi all, We are trying to more efficiently add marketing list members. When accessing the manage members option, the default system look up view provided for contacts includes both active and inactive contacts. We can't easily identify the active only contacts. Is there a way to modify the lookup view to include only active members? Thanks in advance for your help. David Hi David, Yes, there is check out following; http://ronaldlemmen.blogspot.com/2006/12/filter-data-in-crm-lookup-field.html -- PLEASE do click on Yes or No button if this post is helpful or not for our feedback. ...

how do I change list order in color catagory assignment in office
only 15 color catagories can be displayed in color assignment in office outlook 2007. I wish to change the order of the list. I tried to number them 1-15, but it did not change the list. It did show 10-15 in the top of the list, and 1-9 on the bottom. I wish it to show them in order of 1-15, not necessarily with numbers, but with the color and catagory that I have assigne to them. any help would be appreciated THNX ...

How to Change default font /is it possible to retrieve a deleted f
My PC runs on the new Windows 7. 1. Is it possible to retrieve a deleted file? I did look in the recyle area, and my deleted file was not there. 2. How do you change the default font? 1. If the file has been deleted from the Recycle Bin, you'll need special data recovery software. I have to admit that I have no idea how useful that kind of software actually is. Depending on the nature of the deleted document, it might not be worth the effort (or cost). 2. You can choose a font in the Font dialog box (Ctrl+D) and then click the Default button to change the defaul...

copy/paste into excel changes numbers into date format
I need to copy a lot of raw data from html format into excel. Most of the data consists of a min/max figure (eg. 31/45) Excel (2003) always converts this number to date format no matter what I format the cells as. I have been unsuccesful in converting back to the same format. Any help or advice is appreciated. Thanks Hi try formating the cell s as 'Text' before pasting the values -- Regards Frank Kabel Frankfurt, Germany Rufus wrote: > I need to copy a lot of raw data from html format into > excel. > > Most of the data consists of a min/max figure (eg. 31/45) &...

Change text name based on file name
On my spreadsheet in cell A1 I have a text label containing the word "Department" What I want is when I save the file as Schedule 1 (Division).xls I want cell A1 change from "Department" to "Division" based upon what is within the parentheses in this example Any tips will be appreciated. Thank you. If I understand you, you want to return the portion of the wb's name that is within parentheses. If so, try this formula in cell A1. After doing a "Save As..." you will have to press F9 to update. Minimal testing: =MID(CELL("filename&quo...

why do I get a message that says save a copy or overwrite changes
When I try and save a workbook, I get a message that says save a copy or overwrite changes. Why am I getting this message? Did you click File|SaveAs instead of File|Save? Jennifer wrote: > > When I try and save a workbook, I get a message that says save a copy or > overwrite changes. Why am I getting this message? -- Dave Peterson ...

Problems connecting after changing a username
I'm having an issue with a user account. On our Exchange server we have POP3 server running. I set up an user account and tested the settings for the POP3 account. Everything tested fine. I was informed by HR that there was a misspelling in the user's name, so I corrected it, but now when I try to connect to the POP3 server I get an error that says the username, password or e-mail address is incorrect. I can't help but feel this is related to the username change, but I'm not sure how to correct it. Can anyone help? -Michael Kun ...