Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
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" wrote: 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
Oh, okay then try the CountA function instead.
=IF(COUNTA(K4:K10)=0,"",sum(K11:K17)) Mike "Albert Mulder" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
Sweet. You da man.
Works great thank you. "mikebres" wrote: Oh, okay then try the CountA function instead. =IF(COUNTA(K4:K10)=0,"",sum(K11:K17)) Mike "Albert Mulder" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Issue - I have tried to clarify so that you understand
You are welcome. :)
"Albert Mulder" wrote: Sweet. You da man. Works great thank you. "mikebres" wrote: Oh, okay then try the CountA function instead. =IF(COUNTA(K4:K10)=0,"",sum(K11:K17)) Mike "Albert Mulder" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to print on odd and even number page wise Please clarify | Excel Discussion (Misc queries) | |||
Misunderstanding of Objects please clarify | Excel Programming | |||
Ongoing Pivot Table Issue | Excel Discussion (Misc queries) | |||
need to clarify | Charts and Charting in Excel | |||
clarify | Excel Programming |