1

I have a formula that returns average lbs, waist and body fat % for the weeks I've been dieting. The problem is I didn't measure body fat % until a while after I started so some averages are 0. I'd prefer to display N/A instead of 0%, is this possible? And can this formula be made less convoluted? Body fat is in column D.

={query(query({ArrayFormula((if(len(Log!A2:A), quotient(Log!A2:A - min(Log!A2:A), 7) + 1, ))), Log!B2:B}, "select Col1, Avg(Col2) where Col1 > 0 group by Col1"), "select Col1"), ArrayFormula(round(query({ArrayFormula(if(len(Log!A2:A), quotient(Log!A2:A - min(Log!A2:A), 7) + 1, )), Log!B2:B}, "select Avg(Col2) where Col1 > 0 group by Col1 label Avg(Col2) ''"), 1) & " lbs"), ArrayFormula(round(query({ArrayFormula(if(len(Log!A2:A), quotient(Log!A2:A - min(Log!A2:A), 7) + 1, )), Log!C2:C}, "select Avg(Col2) where Col1 > 0 group by Col1 label Avg(Col2) ''"), 1) & " cm"), ArrayFormula(round(query({ArrayFormula(if(len(Log!A2:A), quotient(Log!A2:A - min(Log!A2:A), 7) + 1, )), Log!D2:D}, "select Avg(Col2) where Col1 > 0 group by Col1 label Avg(Col2) ''"), 1) & "%")}
0
  • no need to have multiple ARRAYFORMULA functions in one formula

=ARRAYFORMULA({
 QUERY(QUERY({IF(LEN(Log!A2:A), QUOTIENT(Log!A2:A-MIN(Log!A2:A), 7)+1, ), Log!B2:B}, 
 "select Col1,avg(Col2) where Col1>0 group by Col1"), "select Col1"), 
 ROUND(QUERY({IF(LEN(Log!A2:A), QUOTIENT(Log!A2:A-MIN(Log!A2:A), 7)+1, ), Log!B2:B}, 
 "select avg(Col2) where Col1>0 group by Col1 label avg(Col2)''"), 1)&" lbs", 
 ROUND(QUERY({IF(LEN(Log!A2:A), QUOTIENT(Log!A2:A-MIN(Log!A2:A), 7)+1, ), Log!C2:C}, 
 "select avg(Col2) where Col1>0 group by Col1 label avg(Col2)''"), 1)&" cm", 
 REGEXREPLACE(TO_TEXT(
 ROUND(QUERY({IF(LEN(Log!A2:A), QUOTIENT(Log!A2:A-MIN(Log!A2:A), 7)+1, ), Log!D2:D}, 
 "select avg(Col2) where Col1>0 group by Col1 label avg(Col2)''"), 1)&"%"), 
 "^0%$", "N/A")})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.