Hi Folks
I currently use a standard monthly profit & loss report which gives the monthly totals and cumulative totals as the year progresses. (sample attached)
The monthly variance is simply C4-D4 for December and E4-F4 for January etc.
I would like to know if there is a way these totals can be automatically produced monthly as the various numbers are inputted into the sheet, rather than having to manually enter the changed formulas?
Thanks in advance for any suggestions.
Enter as an array formula (i.e. use CTRL-SHIFT-ENTER)
=INDEX($C$4:$Z$4,MAX(COLUMN($C$4:$Z$4)*ISNUMBER(C4:$Z$4))-2)-INDEX($C$4:$Z$4,MAX(COLUMN($C$4:$Z$4)*ISNUMBER($C$4:$Z$4))-3)
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
Hey Pauleyb
That's just what I wanted, thank you so much.
I've not come across array formulas before, but I'm going to get clued up now!
Would I be correct in assuming that to copy this formula for the next entry down it would neccesitate changing $4 formula entries to $5, and so on?
Much appreciated.
Last edited by basils57; 08-24-2019 at 10:12 AM.
If you want to copy it down, I would recommend changing the original formula's "$4" to just "4". This would allow it to change as you drag it down to lower rows.
Use the 'Evaluate Formula' tool to see how it works. There are other ways to solve this problem, but they all involve a method to find the last data in the row (not including your totals). Using OFFSET and/or dynamic named ranges could be other methods. The problem with array formulae, is that they do take some time, and as the arrays get big, it can bog down excel. With your array size of just 24 cells, that should not be an issue.
Thanks again Pauleyb - I'd already worked out that the $ needed to be excluded, and the other info is very useful.
Hi again
Although in my original post, I said that I wanted the variance of C4-D4, the formula appears to produce D4-C4. What change is require to the formula to produce this please, I can't fathom it?
Last edited by basils57; 08-31-2019 at 11:38 AM.
Try reversing the 2 and the 3 so that the formula reads: =INDEX(C4:Z4,MAX(COLUMN(C4:Z4)*ISNUMBER(C4:Z4))-3)-INDEX(C4:Z4,MAX(COLUMN(C4:Z4)*ISNUMBER(C4:Z4))-2)
Remember to activate by pressing the Ctrl, Shift and Enter keys.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Many thanks for that JeteMc, much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks