#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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.) |
#3
|
||||
|
||||
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
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 |
#4
|
|||
|
|||
Quote:
I will have another look at your solution if I ever get to warp speed. Graeme |
#5
|
|||
|
|||
Quote:
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 |
#6
|
|||
|
|||
Quote:
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. |
#7
|
||||
|
||||
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?
|
#8
|
|||
|
|||
Quote:
=IF(DividorFormula = 0, ExpressionWhen0, YourFormula) =IFERROR(YourFormula,ExpressionWhen0) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
LINK field path will be updated automatically | bynkook | Word | 1 | 02-28-2020 02:54 AM |
change formula based upon change of month | invgrp2 | Excel | 7 | 05-03-2018 10:46 AM |
How to get data updated automatically without show this message? | LearnerExcel | Excel Programming | 2 | 02-12-2018 08:20 PM |
Can i automatically add a debit on the first of every month? | Taras49 | Excel | 2 | 06-16-2015 12:54 PM |
How can references be updated automatically? | ezthelm | Word | 2 | 05-30-2011 03:45 AM |