Ongoing Issue - I have tried to clarify so that you understand

[Albert Mulder]

OK here is what I am trying to do.

I have cells E4 through E10
That some or all Cells could contain a number 0-9, Depending on the users
needs.

I then need to sort them by lowest to highest in F4 through F10

I then need the smallest number entered into G4

Then take the next smallest number and subtract it from the previous
smallest number and enter that into G5 through G10

Next take the result of G4 through G10 and transfer that to H4 through H10
but if H5 through H10 equal “0†then change to “Xâ€.

And if there are only a few cells with numbers make the rest of the cells
Blank – I usually end up with #NUM! – error in the cells if there is no
number to work with

Example:

E4 through E10 is where the user would input a number 0-9
E4 = 7 Data user entered
E5 = No data entered
E6 = 0 Data user entered
E7 = 4 Data user entered
E8 = No data entered
E9 = 5 Data user entered
E10 = 7 Data user entered


F4 through F10 is where it would sort the numbers from smallest to largest
but needs to become a blank cell if no number
F4 = 0 (E6) Code I am using: =SMALL(E4:E10, 1)
F5 = 4 (E7) Code I am using: =SMALL(E4:E10, 2)
F6 = 5 (E9) Code I am using: =SMALL(E4:E10, 3)
F7 = 7 (E4) Code I am using: =SMALL(E4:E10, 4)
F8 = 7 (E10) Code I am using: =SMALL(E4:E10, 5)
F9 = #NUM! (E5) Code I am using: =SMALL(E4:E10, 6)
F10 = #NUM! (E8) Code I am using: =SMALL(E4:E10, 7)

G4 through G10 is where it would take the smallest number and enter it into
the first cell and then the next smallest number and subtract it from the
previous smallest number and enter that into G5 through G10 but needs to
become a blank cell if no number
G4 = 0 (F4) Code I am using: =SUM(F4)
G5 = 4 (F5 minus F4) Code I am using: =SUM(N5-N4)
G6 = 1 (F6 minus F5) Code I am using: =SUM(N6-N5)
G7 = 2 (F7 minus F6) Code I am using: =SUM(N7-N6)
G8 = 0 (F8 minus F7) Code I am using: =SUM(N8-N7)
G9 = #NUM! (F9 minus F8) Code I am using: =SUM(N9-N8)
G10 = #NUM! (F10 minus F9) Code I am using: =SUM(N10-N9)
H4 through H10 take the result of G4 through G10 and transfer that to H4
through H10 but if H5 through H10 equal “0†then change to “Xâ€. but Needs to
become a blank cell if no number
H4 = 0 (G4) Code I am using: =SUM(G4)
H5 = 4 (G5) Code I am using: =IF(G5=0, "X",G5)
H6 = 1 (G6) Code I am using: =IF(G6=0, "X",G6)
H7 = 2 (G7) Code I am using: =IF(G7=0, "X",G7)
H8 = X (G8) Code I am using: =IF(G8=0, "X",G8)
H9 = #NUM! (G9) Code I am using: =IF(G9=0, "X",G9)
H10 = #NUM! (G10) Code I am using: =IF(G10=0, "X",G10)

Hope this helps every one understand what I need.

Yes I need the cells just the way they are. But the Code I am using keeps
giving me Error (#NUM!)

What can I do to fix this?

Please make it easy as I am new to the Excel stuff.

Thank you,

 

[mikebres]

Yoiu could change the formulas in column F to this
=IF(ISERROR(SMALL($E$4:$E$10, 2)),"",SMALL($E$4:$E$10, 2))
this will give you a blank cell if the Small function returns a error such
as #NUM.

and the formulas in column G to this
=IF(F5="","",F5-F4)
Because you have blank cells in column F you need to check for them in your
calculation. If any values in column F are blank then again this will give
you a blank cell in column G.

Mike

 

[Albert Mulder]

That worked great.

How would I do this

I need to add H4 through H10 and check to see if they are all blank and if
so cell H11 needs to be blank.

Just a guess
Cell H11
=IF($H4:$H10="","",$H4:$H10)
But I get error #VALUE!

Also if I wanted to enter a value of 10 onto a cell how would I do that
Example:
E21 = 6 or any number

So then E22 would need to become 10

Cell E22 - again just guessing but did not work.
=IF(E21="","","10")

 

[mikebres]

For the first one a quick method would be

=if(sum(H4:H10)=0,"",sum(H4:H10))

For the second you might try

=IF(NOT(ISBLANK(D9)),10,"")

I used the NOT and IsBlank function to show you an alternative method. You
could also have just tested for Zero.

=IF(NOT(D9=0),10,"") or =IF(D9<>0,10,"")

Mike

 

[Albert Mulder]

Ok I got the second part working but not the first because the user could
enter in "0", so the sum of H4:H10 could be "0" I would need to check it all
H4:H10 were blank not just one, but all.

Something like but that did not work

=IF(ISBLANK($K4:$K10)," ",SUM($K11:$K17))

if nothing is entered it returnes "0"

I must have the ISBLANK($K4:$K10) wrong

What would the correct code be?

 

[mikebres]

Oh, okay then try the CountA function instead.

=IF(COUNTA(K4:K10)=0,"",sum(K11:K17))

Mike

 

[Albert Mulder]

Sweet. You da man.

Works great thank you.

Oh, okay then try the CountA function instead.

=IF(COUNTA(K4:K10)=0,"",sum(K11:K17))

Mike

 

[mikebres]

You are welcome.

Sweet. You da man.

Works great thank you.