New to WIP?
We're a group of makers shipping together. We help each other stay accountable and reach our goals.
Apply for access
Topics
Back
Milestone
Rewrote some convoluted spreadsheet math that was breaking
One of the columns in the spreadsheet has some quite detailed computations, and I was spinning my wheels before finally solving it. I'm not a spreadsheet genius, so getting this sorted was an absolutely HUGE deal.
=ARRAYFORMULA(
IF(
A2:A<>"",
IF(
COUNTIF(Items!A:A, A2:A)>1,
IF(
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
2,
FALSE
) = VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MIN(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
2,
FALSE
),
"All prices the same",
"$" & TEXT(
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
2,
FALSE
),
"#,##0.00"
) &
" per " &
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
3,
FALSE
) &
" " &
VLOOKUP(
A2:A,
QUERY({Items!A:A, Items!E:E, Items!F:F, Items!G:G, Items!H:H}, "SELECT Col1, MAX(Col2), Col3, Col4 WHERE Col1 IS NOT NULL GROUP BY Col1, Col3, Col4", 1),
4,
FALSE
)
),
"N/A"
),
""
)
)
👋 Join WIP to participate