Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Max and MIN question
I have a coulmn with results in that are in a decimal point example .01, .02.
At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#2
|
|||
|
|||
Max and MIN question
What are some of the values in the cells refererred to by the MIN and MAX
functions? and what results are the MIN and MAX functions returning? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron I am sorry I guess I am not following what you want me to try. When I try the basic max and min function they do not return the max and min number. The two columns do not have anything to do with each other just two seperate columns of results. "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#3
|
|||
|
|||
Max and MIN question
Ron
One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#4
|
|||
|
|||
Max and MIN question
Ron At present there are no values I show "". The cells are picking up
information when a water quality test is run. So until that test is run the formula in the cell is as follows =IF('1st'!$U$900,'1st'!$U$90,"") scott "Ron Coderre" wrote: What are some of the values in the cells refererred to by the MIN and MAX functions? and what results are the MIN and MAX functions returning? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron I am sorry I guess I am not following what you want me to try. When I try the basic max and min function they do not return the max and min number. The two columns do not have anything to do with each other just two seperate columns of results. "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#5
|
|||
|
|||
Max and MIN question
Scott
What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#6
|
|||
|
|||
Max and MIN question
Scott, help us with better information. What MIN and MAX formulas are you using? What is the result? If you enter =MIN(i12:i36) what do you get? what do you expect to get? cells that return "<0.05" are probably TEXT entries and can't have a MIN and MAX value. More info, please -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=480689 |
#7
|
|||
|
|||
Max and MIN question
Ron
Hope you read this. The column with this result is is just picking it up off another cell in my work book. The result <0.05 represents the amount of copper that is present in the water quality control test that is run. This number needs to be reported on the state report just exactly like that <0.05. If and when this test is run more than once a month at the bottom of my coulumn I want to pick out the Max number and the Min number that is recorded that month. This also needs to be recorded. Hoep this helps...thanks in adavnce Scott "Ron Coderre" wrote: Scott What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#8
|
|||
|
|||
Max and MIN question
I don't seem to be asking the right question, so I'll ask a few more and
offer some thoughts. Maybe this will help: Are you able to get the correct MAX and MIN? If NO, then what is the stumbling block? Does that list have data for more than one month and you get the MAX/MIN for the whole list, instead of the month you want? If are getting the correct MAx/MIN, are you having trouble returning <0.05? Is that value dependent on the MAX, the MIN, or another number? Could you use something like this?: =IF(MAX(A1:A100)<0.05,"<0.05","something else") Am I anywhere near the right track here? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron Hope you read this. The column with this result is is just picking it up off another cell in my work book. The result <0.05 represents the amount of copper that is present in the water quality control test that is run. This number needs to be reported on the state report just exactly like that <0.05. If and when this test is run more than once a month at the bottom of my coulumn I want to pick out the Max number and the Min number that is recorded that month. This also needs to be recorded. Hoep this helps...thanks in adavnce Scott "Ron Coderre" wrote: Scott What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#9
|
|||
|
|||
Max and MIN question
Ron
I am sorry I will try again. This is a monthly report. In this coulmn I pick data from worksheets of daily test that is run. The resluts of that test is in the form < what ever the number is. The result will not always be <0.05 it may be another number. At the bottom of the coulmn I need to show the MAX number and the Min number for the month. Now I am going to throw this at you also. I also need to total and average these numbers. Now maybe what I am trying to do can not be done. All of our reports have been in lotus. I am in the process of making our daily bench sheets(results of our test) to record on all the different reports that we have to fill out for the month. The old way, operators would run the test and then write in all the info on report sheets and then enter them in manually in the monthly report sheets. This is what I am trying to elimante. This problem maybe if It can not be done someone will have to enter them. Thanks for being patient Scott "Ron Coderre" wrote: I don't seem to be asking the right question, so I'll ask a few more and offer some thoughts. Maybe this will help: Are you able to get the correct MAX and MIN? If NO, then what is the stumbling block? Does that list have data for more than one month and you get the MAX/MIN for the whole list, instead of the month you want? If are getting the correct MAx/MIN, are you having trouble returning <0.05? Is that value dependent on the MAX, the MIN, or another number? Could you use something like this?: =IF(MAX(A1:A100)<0.05,"<0.05","something else") Am I anywhere near the right track here? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron Hope you read this. The column with this result is is just picking it up off another cell in my work book. The result <0.05 represents the amount of copper that is present in the water quality control test that is run. This number needs to be reported on the state report just exactly like that <0.05. If and when this test is run more than once a month at the bottom of my coulumn I want to pick out the Max number and the Min number that is recorded that month. This also needs to be recorded. Hoep this helps...thanks in adavnce Scott "Ron Coderre" wrote: Scott What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#10
|
|||
|
|||
Max and MIN question
Ron
The whole problem is the < sign. I am checking with my supervisor if it is possible to report this with out the < sign. Maybe I can put it in with my column heading that results are reported as <. Unless you have an answer. I will let you know what I find out, thanks scott "Ron Coderre" wrote: I don't seem to be asking the right question, so I'll ask a few more and offer some thoughts. Maybe this will help: Are you able to get the correct MAX and MIN? If NO, then what is the stumbling block? Does that list have data for more than one month and you get the MAX/MIN for the whole list, instead of the month you want? If are getting the correct MAx/MIN, are you having trouble returning <0.05? Is that value dependent on the MAX, the MIN, or another number? Could you use something like this?: =IF(MAX(A1:A100)<0.05,"<0.05","something else") Am I anywhere near the right track here? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron Hope you read this. The column with this result is is just picking it up off another cell in my work book. The result <0.05 represents the amount of copper that is present in the water quality control test that is run. This number needs to be reported on the state report just exactly like that <0.05. If and when this test is run more than once a month at the bottom of my coulumn I want to pick out the Max number and the Min number that is recorded that month. This also needs to be recorded. Hoep this helps...thanks in adavnce Scott "Ron Coderre" wrote: Scott What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#11
|
|||
|
|||
Max and MIN question
SwatspOp
I believe you are right any suggestions of how to get the answer I need. The numbers that are returned in this column will all have < next to them. I need to be able to pick out the max, min, total and average of these. This number that is recorded as < is just being picked off another worksheet. thanks scott "swatsp0p" wrote: Scott, help us with better information. What MIN and MAX formulas are you using? What is the result? If you enter =MIN(i12:i36) what do you get? what do you expect to get? cells that return "<0.05" are probably TEXT entries and can't have a MIN and MAX value. More info, please -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=480689 |
#12
|
|||
|
|||
Max and MIN question
OK, Scott
It seems that the values you are using as the base for your calculations are TEXT. If I understand you correctly, the actual contents of the cell begins with the less-than sign (<). Consequently, the values are NOT numbers; they are TEXT. If that is true, then these formulas might do what you want: =MIN(--MID(IF(ISBLANK($A$1:$A$10),"<99999",$A$1:$A$10),2, 255)) =MAX(--MID(IF(ISBLANK($A$1:$A$10),"<0",$A$1:$A$10),2,255) ) Note: Commit those array formulas by holding down the [Ctrl] and [shift] keys when you press [Enter]. Those formulas strip off the leading character from the cells and convert their values to numbers. I convert blanks to 99999 or zero, depending on whether you are calculating the MIN or MAX (respectively). Does that approach help? -- Regards, Ron "scott45" wrote: Ron I am sorry I will try again. This is a monthly report. In this coulmn I pick data from worksheets of daily test that is run. The resluts of that test is in the form < what ever the number is. The result will not always be <0.05 it may be another number. At the bottom of the coulmn I need to show the MAX number and the Min number for the month. Now I am going to throw this at you also. I also need to total and average these numbers. Now maybe what I am trying to do can not be done. All of our reports have been in lotus. I am in the process of making our daily bench sheets(results of our test) to record on all the different reports that we have to fill out for the month. The old way, operators would run the test and then write in all the info on report sheets and then enter them in manually in the monthly report sheets. This is what I am trying to elimante. This problem maybe if It can not be done someone will have to enter them. Thanks for being patient Scott "Ron Coderre" wrote: I don't seem to be asking the right question, so I'll ask a few more and offer some thoughts. Maybe this will help: Are you able to get the correct MAX and MIN? If NO, then what is the stumbling block? Does that list have data for more than one month and you get the MAX/MIN for the whole list, instead of the month you want? If are getting the correct MAx/MIN, are you having trouble returning <0.05? Is that value dependent on the MAX, the MIN, or another number? Could you use something like this?: =IF(MAX(A1:A100)<0.05,"<0.05","something else") Am I anywhere near the right track here? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron Hope you read this. The column with this result is is just picking it up off another cell in my work book. The result <0.05 represents the amount of copper that is present in the water quality control test that is run. This number needs to be reported on the state report just exactly like that <0.05. If and when this test is run more than once a month at the bottom of my coulumn I want to pick out the Max number and the Min number that is recorded that month. This also needs to be recorded. Hoep this helps...thanks in adavnce Scott "Ron Coderre" wrote: Scott What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#13
|
|||
|
|||
Max and MIN question
Ron
I appreciate your time and sorry I haven't got back. I tried typing in your formula but excell says I have an error and it highlights the second set of numbers which would be your a10, mine is in column i which I understand doens't make a difference. I am going to try and re-enter formula and then try the max but unforunatley I do not have time today and will be in class all day Wed. and Thursday I am also tied up. I probably will not get back to it till Friday. Thanks again for all your help and i hope this works. I will let you know by posting another message if it does. If it doesn't I will be back again. scott "Ron Coderre" wrote: OK, Scott It seems that the values you are using as the base for your calculations are TEXT. If I understand you correctly, the actual contents of the cell begins with the less-than sign (<). Consequently, the values are NOT numbers; they are TEXT. If that is true, then these formulas might do what you want: =MIN(--MID(IF(ISBLANK($A$1:$A$10),"<99999",$A$1:$A$10),2, 255)) =MAX(--MID(IF(ISBLANK($A$1:$A$10),"<0",$A$1:$A$10),2,255) ) Note: Commit those array formulas by holding down the [Ctrl] and [shift] keys when you press [Enter]. Those formulas strip off the leading character from the cells and convert their values to numbers. I convert blanks to 99999 or zero, depending on whether you are calculating the MIN or MAX (respectively). Does that approach help? -- Regards, Ron "scott45" wrote: Ron I am sorry I will try again. This is a monthly report. In this coulmn I pick data from worksheets of daily test that is run. The resluts of that test is in the form < what ever the number is. The result will not always be <0.05 it may be another number. At the bottom of the coulmn I need to show the MAX number and the Min number for the month. Now I am going to throw this at you also. I also need to total and average these numbers. Now maybe what I am trying to do can not be done. All of our reports have been in lotus. I am in the process of making our daily bench sheets(results of our test) to record on all the different reports that we have to fill out for the month. The old way, operators would run the test and then write in all the info on report sheets and then enter them in manually in the monthly report sheets. This is what I am trying to elimante. This problem maybe if It can not be done someone will have to enter them. Thanks for being patient Scott "Ron Coderre" wrote: I don't seem to be asking the right question, so I'll ask a few more and offer some thoughts. Maybe this will help: Are you able to get the correct MAX and MIN? If NO, then what is the stumbling block? Does that list have data for more than one month and you get the MAX/MIN for the whole list, instead of the month you want? If are getting the correct MAx/MIN, are you having trouble returning <0.05? Is that value dependent on the MAX, the MIN, or another number? Could you use something like this?: =IF(MAX(A1:A100)<0.05,"<0.05","something else") Am I anywhere near the right track here? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron Hope you read this. The column with this result is is just picking it up off another cell in my work book. The result <0.05 represents the amount of copper that is present in the water quality control test that is run. This number needs to be reported on the state report just exactly like that <0.05. If and when this test is run more than once a month at the bottom of my coulumn I want to pick out the Max number and the Min number that is recorded that month. This also needs to be recorded. Hoep this helps...thanks in adavnce Scott "Ron Coderre" wrote: Scott What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
#14
|
|||
|
|||
Max and MIN question
Ron
I hope you get this. The formual did not work. It returns a value error even with data entered in cells. "Ron Coderre" wrote: OK, Scott It seems that the values you are using as the base for your calculations are TEXT. If I understand you correctly, the actual contents of the cell begins with the less-than sign (<). Consequently, the values are NOT numbers; they are TEXT. If that is true, then these formulas might do what you want: =MIN(--MID(IF(ISBLANK($A$1:$A$10),"<99999",$A$1:$A$10),2, 255)) =MAX(--MID(IF(ISBLANK($A$1:$A$10),"<0",$A$1:$A$10),2,255) ) Note: Commit those array formulas by holding down the [Ctrl] and [shift] keys when you press [Enter]. Those formulas strip off the leading character from the cells and convert their values to numbers. I convert blanks to 99999 or zero, depending on whether you are calculating the MIN or MAX (respectively). Does that approach help? -- Regards, Ron "scott45" wrote: Ron I am sorry I will try again. This is a monthly report. In this coulmn I pick data from worksheets of daily test that is run. The resluts of that test is in the form < what ever the number is. The result will not always be <0.05 it may be another number. At the bottom of the coulmn I need to show the MAX number and the Min number for the month. Now I am going to throw this at you also. I also need to total and average these numbers. Now maybe what I am trying to do can not be done. All of our reports have been in lotus. I am in the process of making our daily bench sheets(results of our test) to record on all the different reports that we have to fill out for the month. The old way, operators would run the test and then write in all the info on report sheets and then enter them in manually in the monthly report sheets. This is what I am trying to elimante. This problem maybe if It can not be done someone will have to enter them. Thanks for being patient Scott "Ron Coderre" wrote: I don't seem to be asking the right question, so I'll ask a few more and offer some thoughts. Maybe this will help: Are you able to get the correct MAX and MIN? If NO, then what is the stumbling block? Does that list have data for more than one month and you get the MAX/MIN for the whole list, instead of the month you want? If are getting the correct MAx/MIN, are you having trouble returning <0.05? Is that value dependent on the MAX, the MIN, or another number? Could you use something like this?: =IF(MAX(A1:A100)<0.05,"<0.05","something else") Am I anywhere near the right track here? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron Hope you read this. The column with this result is is just picking it up off another cell in my work book. The result <0.05 represents the amount of copper that is present in the water quality control test that is run. This number needs to be reported on the state report just exactly like that <0.05. If and when this test is run more than once a month at the bottom of my coulumn I want to pick out the Max number and the Min number that is recorded that month. This also needs to be recorded. Hoep this helps...thanks in adavnce Scott "Ron Coderre" wrote: Scott What are you looking to do with the <0.05 column? Are those text labels, formulas, or numbers? Are you trying to calculate something from them? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "scott45" wrote: Ron One goof, that is me. The cells where I record max and min I did not have enough decimal points. I formated the cells now that works but my other column where info of <0.05 etc is not working scott "Ron Coderre" wrote: See if I got the basic info right.... Col H: Decimal values (0.01,0.02,0.6, etc). Col I: Corresponding column of descriptors (<0.05, =0.05) You want to calculate the minimum and maximum values from Col I. Could you try something like this?: H12:H36 contains decimal numbers I12: =H12 (copy that formula down to H36) Then, custom format Col B numbers to: [<0.05]"<.5";[=0.05]"=.5";"=.5" I37: =MAX(I12:I36) I387: =MIN(I12:I36) Am I on the right track? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€ ¢ Regards, Ron "scott45" wrote: I have a coulmn with results in that are in a decimal point example .01, .02. At the bottom of the coulumn I want to report the max number and the min number but it does not. Also in one column my results are listed as <0.05 etc. At the bottom of this column I want to report the max and min but it does not. I believe because they are not whole numbers. Is there a way to report these numbers. I tried an IF formula where if <0, max(i12:i36) but that did not work. thanks is adavance scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|