Originally published at: See Your Spending As Hours Of Work With This Simple Formula
This simple formula for Google Sheets instantly translates the cost of everything you buy into the hours you worked to pay for them.
This is very clever. I added it to my sheet.
Modified to show as # h : # m (hours : minutes) using custom number format
[h]" h : âmâ m"
If you do this, you have to divide the result by 24 hrs first: see below from the example in the article.
=arrayformula(if(row(E:E)=1,"Hours to Earn",(if((D:D)="Credit Card Payment","",(if((E:E)<0,(E:E)/-33/24,""))))))
This is awesome!
Arrayformulas donât work with and/or statements afaik - so if you wanted to select âcredit card paymentâ or âamazon store cardâ, for example, then youâd need
=ARRAYFORMULA(if(row(E:E)=1,"Hours to Earn",if(if((G:G)="Credit Card Payment",1,0)+if((G:G)="Amazon Store Card",1,0)> 0,if((E:E)<0,(E:E)/-33,""),"")))
And then you could continue to + IF statements for other ORs
=ARRAYFORMULA(if(row(E:E)=1,"Hours to Earn",if(if((G:G)="Credit Card Payment",1,0)+if((G:G)="Amazon Store Card",1,0)+(if((G:G)="Secondary Credit Card Payment",1,0)> 0,if((E:E)<0,(E:E)/-33,""),"")))
AND statements get a * instead of a +
In my transactions I realized that I really donât want to see ANY hours calculation for anything tagged âTransferâ (this covers all credit card payments, intra-account movement, dividend reinvestment, etc). I anticipated that Iâd want to exclude other categories as well. Based on this I ended up changing that IF() to a SWITCH() looking at the G column (where my categories are set). Formatted for readability:
=arrayformula( if(row(D:D)=1,"Hours to Earn",( switch((G:G), "Transfer", "",( if((D:D)<0,(D:D)/-33,"") ) ) ) ) )
But after making this change I realized that âTransferâ (so far) seems to cover everything I donât care about. But if I DID want to start adding more exclusions from the G column Iâd insert them after the second comma in "Transfer", "",
such as:
=arrayformula( if(row(D:D)=1,"Hours to Earn",( switch((G:G), "Transfer", "", "OtherGroup", "", "ThirdGroup", "",( if((D:D)<0,(D:D)/-33,"") ) ) ) ) )
If I wanted to stick with logical expressions, to check different columns, etc (as rory.kai.foulger is doing above) then Iâd have considered switching from IF() to IFS() (essentially IF combined with SWITCH functionality). This would allow a more complex evaluation to be done.
I feel like if I wanted to spend more time on this then the hourly pay should just be consumed from a value entered into a cell somewhere, the exclusions list should consume a list into an array to be checked against, and the âHours to earnâ header calculation, the exclusions, and the money->hours calculation should all be split out as custom functions that are then strung together. I think this would do wonders for readability.
I did something similar. My Transaction sheet has a Group/Type columns so I just switched to âTransferâ in there.
I really like this calculation. Itâs âsimpleâ but it does show what is necessary. I can now also get more input if I want to buy a new âtoyâ and visualize how long it would take me to work for it, which is a great way to cut back on discretionary spending.
Interested in using this but it doesnât work for me for some reason. My columns are set up correctly according to the original formula and I just get a REF error. Category is Column D and Amount is Column E. Any thoughts for the group?
edit: Found the issueâŚwas putting formula in F2 and not F1. Goodness, I am not bringing my A game lately.