for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

Diogenes Nov 2018 11:04am, 16 Nov 2018 32,075 posts	Quote Pin Diogenes Actually, that might get me near enough - let's see
Diogenes Nov 2018 11:21am, 16 Nov 2018 32,076 posts	Quote Pin Diogenes Aaargh! Not all of them have a space Some somehow have different numerical values under one field, which shouldn't be possible I might be banging my head against a brick wall
Fitz Nov 2018 1:01pm, 21 Nov 2018 1,958 posts	Quote Pin Fitz Hello Excellers, I want to find the MIN value of a range of data, but to treat blank cells as zeroes rather than being ignored. I've cracked it by doing this: =IF(COUNTBLANK(G$30:G$42)>0,0,MIN(G$30:G$42)) But I wonder is there a neater way of doing it?
GlennR Nov 2018 1:04pm, 21 Nov 2018 39,218 posts	Quote Pin GlennR The 'proper' way to do it is to force zeros into the blank cells. If you have a range of data, then it should all be driven by the same formula.
Fitz Nov 2018 1:27pm, 21 Nov 2018 1,959 posts	Quote Pin Fitz Thanks GlennR. I could go back and change (find/replace) all my blanks to zeroes, then format the cells to display zeroes as blanks (because I prefer them to be blank than occupied by 0) but that seems as inelegant as my fudged formula!
Fitz Nov 2018 1:30pm, 21 Nov 2018 1,960 posts	Quote Pin Fitz "Zeros", not "zeroes" - everyday's a school day. Except Saturday, Sunday, public holidays and school holidays.
flanker Nov 2018 2:11pm, 21 Nov 2018 18,007 posts	Quote Pin flanker You could combine the two approaches using a (hidden if you like) helper column that transposes blanks -> zeros and then you could just use MIN(:) against this.
Fitz Nov 2018 2:43pm, 21 Nov 2018 1,961 posts	Quote Pin Fitz Yes, could do that flanker. Thanks
GlennR Nov 2018 2:55pm, 21 Nov 2018 39,235 posts	Quote Pin GlennR I do have a fairly fundamental objection to running blanks into a numerical cell.
Fitz Nov 2018 3:10pm, 21 Nov 2018 1,962 posts	Quote Pin Fitz I hear you GlennR but it's just a record of household utility bills, mobile phone usage, etc. No decisions about single markets, customs unions or backstops will be made on the strength of this data.