LinkBack URL
About LinkBacks
Hello all,
Nice easy one for you all.
I would like to work out my % increase or decrease per year, but only to the month to date. Not my total.
See attachment it might make it a little clearer.
Thank you in advance.
Hello all,
Nice easy one for you all.
I would like to work out my % increase or decrease per year, but only to the month to date. Not my total.
See attachment it might make it a little clearer.
Thank you in advance.
Hi
Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc.
Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.
Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Please see attached - hope this explains it better.
try:
Formula:
Please Login or Register to view this content.
I can't get it to work.
Can you drop it into my spread sheet sample I've sent you and re-attach.
the percentage doesn't look right to me when I do it . . . .
Hi All,
Can anybody else help me with this issue??
Hold your horses!! A little patience goes a long way...Originally Posted by ExcelnoviceUK
See attachment - your file, with my formula added, in cell P4.
It shows -17.61% - is that not correct?
2014-15 sales (to Sep) = $1,531,025.57
2015-16 sales (to Sep) = $1,261,344.00
-17.61% sounds right to me...
Ha ha sorry for being impatient.
As you can probably tell from my name I'm a bit of a novice to excel - learning everyday.
I've looked at the formula you inputted and the percentage looks correct to me, however I don't know how the formula works to get this. Please could you try and explain this to me?
When I click into the cell you've put the formula into it only highlights C3, but on the C4 it looks at the whole line.
I'm using the OFFSET function to dynamically specify the sum range for 2014-15, based on the number of months with sales value in 2015-16.
If you're still not really following it, I'd suggest using the EVALUATE FORMULA command on the FORMULA ribbon.
Still Don't understand how it works - but its giving me the correct information so that's all that matters.
Thank you very much for the help and for being patient.
We're trying to establish: 2015-16 (to date) Sales as a percentage of 2014-15 Sales (for the same period), -100% to show only the variance %.
So we need to calculate TWO numberes:
1. 2015-2016 Sales. This is easy - just this part:
Formula:Please Login or Register to view this content.
2. 2014-2015 Sales, for the same period. This is a bit trickier. We only want to count the number of months for which there are sales in 2015-2016.
a. So first of all, we count how many months in 2015-2016 have values:
Formula:Please Login or Register to view this content.
b. Now we want to sum from C3:?3 - ? is determined by the count, in 2a. So we use the OFFSET function, to specify a dynamic range:
(where x is the count we calculated in 2a)Formula:Please Login or Register to view this content.
OFFSET specifies a starting cell, how many rows offset (0, in this case), how many columns offset (0, in this case), the number of rows in the returned range(1, in this case) and the number of columns in the returned range (x, in this case, where x is the count of months with sales values in 2015-2016)
Any clearer?!
Helping to make sens of it?
You can do it with SUMIF as well.
=SUM(C4:N4)/SUMIF(C4:N4,"<>",C3:N3)-1
Simples
Hi Olly,
Yes when you break it down like that it defiantly makes more sense.
Sorry to be a pain, instead of just putting formulas in, I'm trying to establish an understanding of them also.
I really appreciate your help.
Fantastic - Appreciate the reply's and help guys!!!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also Add Reputation to those who have helped you. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks