Blank Cell being largest

[Lewzerrrr]

Hi,

I have a sheet at work in which I need to sort/filter largest to smallest, I've tried to replicate it at home.

Just a side question, how comes when I pop a filter over column F it it comes up sort A-Z, sort Z-A when that column has numbers in instead of text? My replica at home has the same formula which returns numbers but says sort Largest to Smallest, sort Smallest to Largest?

So in column F I have a formula that says, =IF(B2="","",IFERROR(SUM(H2/G2),99.9))).

What I'm trying to do is neaten up the work sheet so if my data input has nothing it then the IF will return that column as BLANK but if I input my data then the IF won't be blank so it will return my sum.. and I drag that down incase of large data input but when I sort it so the numeric value is largest to smallest, the blanks appear first? (I know I can easily filter out blanks)

	A	B	C	D	E	F
1	Picked	Product	Description	Units	MD	LW Cover
2		PROD11	NIHG	180		99.9
3		PROD8	KH	167		5.0
4		PROD5	RD	154	Y	99.9
5		PROD10	NUBF	141		1.5
6		PROD4	GUG	128		99.9
7		PROD12	KN	115		99.9
8		PROD7	IYUYB	102		1.3
9		PROD2	NUBF	89	Y	3.6
10		PROD3	IHIHUG	87		0.5
11		PROD9	HI	65		99.9
12		PROD1	DUSKY BLOUSE	62		0.3
13		PROD6	LOL	10		1.5
14
15
16

<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[DILIPandey]

Are you sure... your numbers are not in text format ?


Regards,
DILIPandey

 

[Lewzerrrr]

Yes, I just done ISNUMBER and they all return true. I just tried to use ISBLANK on what should be blank cells and its returning false. Can you advise on what the cell is then if not blank?

 

[Lewzerrrr]

An ISTEXT returns true on the =IF(B2="","",IFERROR(SUM(H2/G2),99.9)).. so what is the [value_if_true] doing which is "", if it's not a blank?

 

[Lewzerrrr]

EDIT: Instead I put my [value_if_true] as -9998 and then conditional format on that row for cells that contain cell value between -9997 and -9999 with white text.

 

[DILIPandey]

If a cell is having formula then it is not BLANK and hence isblank will return false.


Regards,
DILIPandey

 

[DILIPandey]

So based on your data, which column out of 'units' and 'cover' puts blanks on top if your sort numeric - largest to smallest ?




Regards,
DILIPandey