Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-01-2020, 04:09 PM
GraemeSS GraemeSS is offline Find % change from last month automatically updated Windows 10 Find % change from last month automatically updated Office 2019
Novice
Find % change from last month automatically updated
 
Join Date: Apr 2020
Posts: 6
GraemeSS is on a distinguished road
Default Find % change from last month automatically updated


Hi Gurus,
I want to know percentage change from last month (C6-D6)/C6. But I want it to be displayed in a column at the end and to automatically update each time I add the monthly data.

I'm using offset to find the latest data

OFFSET(B6,0,COUNTIF(C6:AF6,">.00"))


How do I combine them to find the latest percentage change from last month each time I add new data.



I am not the greatest whiz at Excel but here is how I solved the rolling 12 months average.

=IFERROR(AVERAGE(OFFSET(B6,0,COUNTIF(C6:AF6,">.00" ),1,-12)),"")


Would love to hear from you in these dark days.
Graeme
Reply With Quote
  #2  
Old 04-01-2020, 11:04 PM
ArviLaanemets ArviLaanemets is offline Find % change from last month automatically updated Windows 8 Find % change from last month automatically updated Office 2016
Expert
 
Join Date: May 2017
Posts: 564
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

Here you go!

There are 3 variations of same formula. In 2nd one I replaced subformulas of 1st one with dynamic Names, to make the formula on sheet more readable. The 3rd one is copy of 1st one, but it uses dynamic Named Ranges as source instead on Table (it's for case you don't want to use defined Tables).

You can replace month number in current format with date of 1st of month, and format it then in any way too. But remember, the values you use for months must grow in right order! (I.e. texts like "January 2020" and "February 2020" will not do.)
Attached Files
File Type: xlsx LastEntryCalc.xlsx (10.6 KB, 4 views)
Reply With Quote
  #3  
Old 04-02-2020, 04:40 AM
p45cal p45cal is online now Find % change from last month automatically updated Windows 10 Find % change from last month automatically updated Office 2019
Expert
 
Join Date: Apr 2014
Posts: 389
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Quote:
Originally Posted by GraemeSS View Post
I want to know percentage change from last month (C6-D6)/C6.
I'm not sure this'll give that, shouldn't it be (D6-C6)/C6 ?
I've assumed later months are to the right.
This can be shortened to:
D6/C6-1

Anyway, I leave that to you by tweaking that bit in these formulae.
Using the same basis for finding the last month:
Code:
=OFFSET(B6,0,COUNTIF($C6:$AF6,">.00"))/OFFSET(B6,0,COUNTIF($C6:$AF6,">.00")-1)-1
This will give the wrong answer if you have any or more of the following in the cells to the left of last entry:
  • spaces/blank cells
  • errors
  • zeroes
  • negative numbers
  • text
Significantly safer, but longer and more difficult to maintain:
Code:
=(INDEX($A6:$AF6,AGGREGATE(14,4,(NOT(ISBLANK($B6:$AF6))*COLUMN($B6:$AF6)),1))/INDEX($A6:$AF6,AGGREGATE(14,4,(NOT(ISBLANK($B6:$AF6))*COLUMN($B6:$AF6)),1)-1))-1
Reply With Quote
  #4  
Old 04-02-2020, 04:41 PM
GraemeSS GraemeSS is offline Find % change from last month automatically updated Windows 10 Find % change from last month automatically updated Office 2019
Novice
Find % change from last month automatically updated
 
Join Date: Apr 2020
Posts: 6
GraemeSS is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Here you go!

There are 3 variations of same formula. In 2nd one I replaced subformulas of 1st one with dynamic Names, to make the formula on sheet more readable. The 3rd one is copy of 1st one, but it uses dynamic Named Ranges as source instead on Table (it's for case you don't want to use defined Tables).

You can replace month number in current format with date of 1st of month, and format it then in any way too. But remember, the values you use for months must grow in right order! (I.e. texts like "January 2020" and "February 2020" will not do.)
Thank you ArviLaanemets, Unfortunately I am not orbiting the same planet as you in terms of my excel skills. I am struggling to get my single prop biplane off the ground.

I will have another look at your solution if I ever get to warp speed.


Graeme
Reply With Quote
  #5  
Old 04-02-2020, 04:53 PM
GraemeSS GraemeSS is offline Find % change from last month automatically updated Windows 10 Find % change from last month automatically updated Office 2019
Novice
Find % change from last month automatically updated
 
Join Date: Apr 2020
Posts: 6
GraemeSS is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
I'm not sure this'll give that, shouldn't it be (D6-C6)/C6 ?
I've assumed later months are to the right.
This can be shortened to:
D6/C6-1

Anyway, I leave that to you by tweaking that bit in these formulae.
Using the same basis for finding the last month:
Code:
=OFFSET(B6,0,COUNTIF($C6:$AF6,">.00"))/OFFSET(B6,0,COUNTIF($C6:$AF6,">.00")-1)-1
This will give the wrong answer if you have any or more of the following in the cells to the left of last entry:
  • spaces/blank cells
  • errors
  • zeroes
  • negative numbers
  • text
Significantly safer, but longer and more difficult to maintain:
Code:
=(INDEX($A6:$AF6,AGGREGATE(14,4,(NOT(ISBLANK($B6:$AF6))*COLUMN($B6:$AF6)),1))/INDEX($A6:$AF6,AGGREGATE(14,4,(NOT(ISBLANK($B6:$AF6))*COLUMN($B6:$AF6)),1)-1))-1

Thank you so much p45cal! The formula works perfectly (and of course you were right I had made a mistake in my 5th grade % formula).



Just to give you some background, I am setting up a report to show telephony call, duration and cost data. Then from that average duration and average cost per call. I want to have charts for a visual representation.


I have tried to set up my sheet to populate all the different tabs from a single source for two and a half years.



Problem is when I have formulas looking for data from the other sheets it assumes the next column has zero and I get a Div/0 error. How do I get the formula to remove the zero if there is no data?


In your debt, Graeme
Reply With Quote
  #6  
Old 04-02-2020, 11:56 PM
ArviLaanemets ArviLaanemets is offline Find % change from last month automatically updated Windows 8 Find % change from last month automatically updated Office 2016
Expert
 
Join Date: May 2017
Posts: 564
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

Quote:
Originally Posted by GraemeSS View Post
I will have another look at your solution if I ever get to warp speed
This isn't too difficult really. Formulas used in my example are ordinary Excel formulas. The main difference is, that they adjust automatically whenever data is added/deleted to/from table.

3rd formula is like I'd use before defined Tables feature was introduced. The dynamic named range nrData is defined (Formulas>Name Manager from menu). Select Name from leftmost column, and in field 'Refers to' you see the formula which defines the range. click on this formula, and in table on worksheet you see the datarange of table selected (dashed border).
The formula is '=OFFSET(Sheet1!$A$5;1;;COUNT(Sheet1!$A:$A);4)'. An ordinary Offset() formula.
Formula parameters:
1st is SheetName!$A$5. It sets anchor point for range definition to cell $A$5 on sheet with table (the anchor must be outside of datarange, so formula will not be broken whenever 1st row from datarange is deleted);
2nd is 1. It moves the top row reference of range you are defining from anchor's row to topmost row of a datarange in table;
3rd is empty, i.e. leftmost column of range you are defining remains same as anchor's column;
4th is COUNT(SheetName!$A:$A). It counts all numeric values in column A. Assumed there is no numeric values in this column except in table's datarange, and there is no entry in table where the value of Month is not numeric, this returns the number of rows in table. And in OFFSET(), it determines how many rows the range will have;
5th is 4. This determines, how many columns the range will have. It is possible to count header row entries here too using COUNTA(), but let's keep it simple.

A dynamic range defined in such way adjusts automatically whenever rows are added to table or deleted from it. And when you use in your other formulas this dynamic range instead references like 'SheeName!$A$6:4D7, then whenever the table is edited, you don't need to edit your formulas manually. Another bonus is, that you can give a meaningful name to every such range, so when you look at formulas, you don't need to navigate to source table to look, which kind of data your are using as source.

I defined 3 additional named ranges (nrMonth, nrVal3, nrVal4), which simply define separate columns of nrData as named ranges. And as nrData is dynamic, those 3 are dynamic too.

To calculate the percentage in D3, values of Val3 and Val4 from row with max value of month must be used. I used SUMIFS() to calculate those values (you can use here SUMIF() too, but I prefer SUMIFS() everywhere, as it is more universal).



1st formula for percentage uses defined Tables. You can define a table as Table (table must have a single header row!), selecting a single cell in table header or datarange, and then from menu Insert>Table. You can give defined Table a name.

A defined Table is essentially a dynamic named range (it expands automatically, whenever you add data into cells bordering with it) with special features. You can use special Table syntax in formulas, which refer to various elements of table (whole table, header row, datarange, column data, value in another column in same row, etc.). A specific Table feature is, that when whole column in Table contains same formula, then whenever rows are added, the formula expands to new rows automatically. And when in formlulas you refer to data in Table using only Table syntax, you can add and delete columns (when deleting, of-course you must not have references to this column in any of formulas!) freely, change Table name or Table column names, and change Table column positions (activating column, pressing Shift, and holding Shift down dragging column to new position) - in all formulas in workbook referring to Table all changes will be updated automatically.

Later you can access some Table features (Name, Size, etc) selecting any Table cell, and activating Design from menu.

Rest of it is essentially same as with Named Ranges.
Reply With Quote
  #7  
Old 04-03-2020, 02:13 AM
p45cal p45cal is online now Find % change from last month automatically updated Windows 10 Find % change from last month automatically updated Office 2019
Expert
 
Join Date: Apr 2014
Posts: 389
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Quote:
Originally Posted by GraemeSS View Post
Problem is when I have formulas looking for data from the other sheets it assumes the next column has zero and I get a Div/0 error. How do I get the formula to remove the zero if there is no data?
I've not understood - could you attach a little spreadsheet with a few rows of data and as many sheets as necessary and few rows, some showing the formula (which formula?) working properly, some with the division by zero error?
Reply With Quote
  #8  
Old 04-03-2020, 02:50 AM
ArviLaanemets ArviLaanemets is offline Find % change from last month automatically updated Windows 8 Find % change from last month automatically updated Office 2016
Expert
 
Join Date: May 2017
Posts: 564
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

Quote:
Originally Posted by GraemeSS View Post
Problem is when I have formulas looking for data from the other sheets it assumes the next column has zero and I get a Div/0 error
Generally there are 2 ways:
=IF(DividorFormula = 0, ExpressionWhen0, YourFormula)
=IFERROR(YourFormula,ExpressionWhen0)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find % change from last month automatically updated LINK field path will be updated automatically bynkook Word 1 02-28-2020 02:54 AM
Find % change from last month automatically updated change formula based upon change of month invgrp2 Excel 7 05-03-2018 10:46 AM
Find % change from last month automatically updated How to get data updated automatically without show this message? LearnerExcel Excel Programming 2 02-12-2018 08:20 PM
Find % change from last month automatically updated Can i automatically add a debit on the first of every month? Taras49 Excel 2 06-16-2015 12:54 PM
Find % change from last month automatically updated How can references be updated automatically? ezthelm Word 2 05-30-2011 03:45 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft