google-sheetsgoogle-sheets-formula

Need to turn a simple formula into an array formula

I am trying to turn a simple "IF" formula into an array. Here is the formula I'm using:

=IF(H88>0,(H88/$H$103),"")

I'm finding that when I add "Arrayformula(" to the front of the formula, it goes bezerk! The responses are in the wrong cells, etc.

My ultimate goal is to make a budget workbook for our home that other people like me (one's who can't leave well enough alone) can figure out and use easily. To do that I'm attempting to have all formulas in the "column header" cell. Nobody delete's the "title" of the column...

Here's a sample worksheet: text

I've tried:

=ARRAYFORMULA(VSTACK("Percentage of BUDGET", IF(B88:B102<>"", ROUND(SUMIF(H88:H102>0,(H88/$H$103),"")
=ARRAYFORMULA(VSTACK("Percentage of BUDGET", IF(B88:B102<>"",SUM(H88/$H$103),"")))

...and many other variations of the above with no luck. I just can't seem to 'get' arrays. I also tried using 'MAP' and 'VStack' but nothing is working out!

Solution

  • Use ARRAYFORMULA() and VSTACK()

    You can use either of the two formulas and paste it into cell J6

    =Arrayformula(vstack("Percentage of BUDGET", IF(H7:H21>0,(H7:H21/$E$22)*100%,"")))

    =IFNA(VSTACK("Percentage of Budget",ARRAYFORMULA(IF(H7:H21<0.1,,(H7:H21/$E$22)*100%))))

    References: