The Money Mustache Community

Learning, Sharing, and Teaching => Ask a Mustachian => Topic started by: Penn42 on March 05, 2018, 09:41:52 PM

Title: Excel Gurus In The House?
Post by: Penn42 on March 05, 2018, 09:41:52 PM
I'm attempting to incorporate parts of the Mad Fientist's FI spreadsheet into my own and I've run into a bug. 

Say I have a range of cells A1:L1.  A1 being January and L1 being December.  I want to average the range of cells up to the current month.  At first I thought I could accomplish this by doing AVERAGEIF(A1:L1, ">0").  But... lets pretend row 1 is for rent and lets also pretend I did a favor for my landlord and he waived my rent for February.   

If we fast forward to December and I have 11 cells with a rent amount in them the average of those eleven cells, when multiplied by 12 months,  will not equal my total spending on rent that year because my average monthly cost of rent needs to have a divisor of 12, not 11.

Is there a way I could get excel to include that 0 in the B1 cell and not, at this point in the year, cells D1:L1? 

I'm looking to have excel give me an approximate forecast of my expenses in various categories for the year based on my YTD spending in said categories.  The reason all the forthcoming months have values of zero is that I have those cells pulling information from other pages I have devoted to my actual budgeting and spending. 
Title: Re: Excel Gurus In The House?
Post by: terran on March 05, 2018, 10:16:15 PM
Doesn't AVERAGE do what you want all by itself? At least in Libreoffice it leaves blank cells out of the average calculation, but includes cells with a zero entered. So if you have everything but Nov/Dec entered, with a zero in Feb it will sum everything and divide by 10.
Title: Re: Excel Gurus In The House?
Post by: Penn42 on March 06, 2018, 06:40:53 AM
Yeah, the problem with that is all cells currently have a zero in them because they're all linked to the budgeting pages and I haven't entered any data for anything past march yet.  It would all shake out by the end of the year, but currently it would spit out an artificially low annual forecast from dividing two months worth of expenses by a years worth of months.

It's totally possible what I'm looking to have it do isn't possible and I'll have to make the sheet a little more manual.  It's just be so cool if I could get all this to work 100 percent automatic.
Title: Re: Excel Gurus In The House?
Post by: jlcnuke on March 06, 2018, 07:39:03 AM
use an "IF" command to change the zeros to blanks instead? would that fix your problem? I.e. '=IF("your date cell">TODAY(), "", "your normal cell value/calculation")
Title: Re: Excel Gurus In The House?
Post by: Heroes821 on March 06, 2018, 07:50:50 AM
Why not just set Feb to 1 or 0.01?

It should still work out without making you "fix" the formula that would otherwise do what you want in a normal year?
Title: Re: Excel Gurus In The House?
Post by: ToeInTheWater on March 06, 2018, 07:54:57 AM
AVERAGEIFS will work, if you have a row of dates in there

so, if row 1 is the month (as dates, not text)
and row 2 was your rent - calcs assume $100 for all months except Feb

=AVERAGEIFS(A2:L2,  A1:L1, "<="&TODAY() )

would return $66.67 if you calc it during March
and $75 if you do so in Apr

is that what you're looking for?

b
Title: Re: Excel Gurus In The House?
Post by: Penn42 on March 06, 2018, 08:06:16 AM
Thanks for the suggestions everyone.  I'm learning lots about these functions as I go so I'll have to mess around with these once I'm off work today and tomorrow.  Will report back.
Title: Re: Excel Gurus In The House?
Post by: MDM on March 06, 2018, 08:06:48 AM
Say I have a range of cells A1:L1.  A1 being January and L1 being December.
...
I'm looking to have excel give me an approximate forecast of my expenses in various categories for the year based on my YTD spending in said categories.
If cell M1 has =sum(A1:L1), then putting
=M1*365/(DAYS(NOW(),"12/31/2017"))
in cell N1 will give you what you seek.
Title: Re: Excel Gurus In The House?
Post by: catccc on March 06, 2018, 09:13:15 AM
there are more ways than one when it comes to accomplishing stuff in excel.  I personally would combine the index function with average, like this:

=AVERAGE(A1:INDEX(A1:L1,$T$1))

where your data for the year is in A1 thru L1, and far off to the right you have cell T1, in which you enter a number between 1 and 12 to indicate how far along the range A1:L1 you want to use for your average.  This way if I want to look at the YTD average for a prior period I can manipulate it, rather than relying on the system date.

I have the $ in there because this is a personal finance forum.  JK.  It's because in the spreadsheet I use this in, I am calculating the average the same way on multiple rows, so I if I copy the formula down, the index function argument always refers to cell T1, no matter what row I am copying to.  If I copy the formula to the row below, it would read =AVERAGE(A2:INDEX(A2:L2,$T$1)).  Sorry if you already knew that.

Title: Re: Excel Gurus In The House?
Post by: Penn42 on March 07, 2018, 09:27:24 PM
Ok.  Got it figured out tonight. 

AVERAGEIFS will work, if you have a row of dates in there

I ended up using the above suggestion.  Took me a while to figure out it was an IFS function and not IF.  And then it took me a while to understand why it mattered.  If I understand: since I'm specifying a separate range of cells as my criteria (A1:L1) I have multiple "ifs" so AVERAGEIF doesn't like it.  Correct?  Also, what purpose does the "&" have between "<=" and Today()?  It doesn't work without it, but I don't get what it's doing.

=AVERAGE(A2:INDEX(A2:L2,$T$1)).  Sorry if you already knew that.

I did not know that, though I had seen it done before.  Just wasn't sure what it mean't. Thanks!

use an "IF" command to change the zeros to blanks instead? would that fix your problem? I.e. '=IF("your date cell">TODAY(), "", "your normal cell value/calculation")

This is a nifty solution too.  However, I couldn't get the false value portion correct.  If I put my normal cell calculation in quotes I'd just get "=SUM(March.B17)" in my cell and if I don't put it in quotes I'd get an Err:510.  Couldn't seem to get it to read the function inside the function.

If cell M1 has =sum(A1:L1), then putting
=M1*365/(DAYS(NOW(),"12/31/2017"))
in cell N1 will give you what you seek.

This one was the easiest to figure out, but I decided to challenge myself, haha.  I still learned how to use the days function!

Title: Re: Excel Gurus In The House?
Post by: Penn42 on May 09, 2018, 07:02:45 PM
Guys, I'm trying to SUMIF across multiple sheets.  Here's what I want:

=SUMIF(Sheet3.A1:Sheet6.A1, "apples", Sheet3.B1:Sheet6.B1)

To me that reads IF cell a1 on Sheets 3-6 IS "apples" then SUM cell B1 from sheets 3-6.  I keep getting an err:504.  What parentheses or something am I missing?  I've been trying to figure it out for 30 minutes now with no luck.
Title: Re: Excel Gurus In The House?
Post by: MDM on May 09, 2018, 08:33:27 PM
Guys, I'm trying to SUMIF across multiple sheets.
You might try one of the suggestions at Excel formula: 3D SUMIF for multiple worksheets | Exceljet (https://exceljet.net/formula/3d-sumif-for-multiple-worksheets).