@Anonymous wrote:I noticed an anomaly, the first row of calculations under all accounts works in the second file but not in the first
@ccquest Microsoft office professional+ 2016
First row is all "regular" functions, second row begins the various IFs.
If you can, try to set B5 to be:
=TEXT(MINIFS($J$18:$J$103,$B$18:$B$103,"Revolver")/SWITCH($A$5,"Months",1,"Years",12),"0.00") & SWITCH($A$5,"Months"," months","Years"," years")
Maybe it'll let you save it that way, but won't load them in for some reason. Basically the little _xl whatever towards the start is breaking it on mine, but because my license is having issues due to the 365 update I can't actually do anything, how nice!
as of 1/1/23
@Anonymous wrote:
What command should be there rather than the ones your named?
The one I quoted above is an example, but here's a Microsoft page on the issue I'm having at least. With Pro Plus 2016 you should have them all working though.
edit » oh, MINIFS is another new function duh. This page says Excel 2019.
@Anonymous, @ccquest : Which version of Excel are you both using?
I have Microsoft 365, so it's always the latest version, which I suspect ccquest is using (or Excel 2019, first standalone version to include the SWITCH statement).
I think this might help a lot of people, but version control might fry ccquest's mind. lol (It would certainly kill me after using Git for so long.)
I've never looked into using GitHub for Excel files, because I was always sharing with other Microsoft 365 users.
There is a paid subscription option, using xltrail (Link), but that's crazy for this. Maybe there is a free, open-source option out there?
Anyway, this is a great start for an Excel worksheet. I would use the following formula for any 'Average Age' calculations that display in 'Years' (as the SWITCH function uses in this worksheet):
=INT(avgCell/12)&"yr "&ROUND(MOD(avgCell,12),0)&"mo"
where avgCell is the average age in straight number format with fractional part.
...using SWITCH:
=@SWITCH($A$5,"Months",AVERAGE(J18:J103),"Years",(INT(AVERAGE(J8:J103)/12)&"yr"&ROUND(MOD(AVERAGE(J8:J103),12),0)&"mo"),"0.00")
( AVERAGE(J18:J103) can be defined as a name in Name Manager)
[EDIT: It looks like this after that change, just in AAoA for now]
I'll have it ready shortly and also trying to incorporate CassieCard's idea too.
@Anonymous give this one a shot please
I think I changed them all, this doesn't include the display change like @Anonymous suggested yet though.
No now I’m getting the error in front of the switch command