small function, but don't count blanks

[jimblimm]

My data

A1:a8

00

02
04

56

99

I am trying to make a small( function work that will not count blank cells

my values in b1:f1 should be

b1=00, c1=02, d1=04, e1=56, f1=99

 

[VoG]

Try in B1 copied across

=SMALL($A1:$A8,COLUMN()-1)

 

[jimblimm]

it gives a num error

 

[VoG]

Working here. My values are numbers formatted as Custom, "00". If you have text values it will fail

Excel Workbook
	A	B	C	D	E	F
1	01	01	02	04	56	99
2
3	02
4	04
5
6	56
7
8	99
CA Invoices

 

[jimblimm]

I get my values in a: from formulas, how should i format them.. (sum then cell a?

 

[VoG]

Can you post the formula?

 

[jimblimm]

A1='00(i know of no other way to display double 0
B1= if (c1=0,a1,"")

C1=0

 

[VoG]

'00 is text not a number so you won't be able to perform math with it.

Just enter 0, select the cell, press CTRL + 1, click Custom, in the box type 00 and click OK.