Max and MIN question

[scott45]

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

[Ron Coderre]

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

[scott45]

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

[scott45]

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$90>0,'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

[Ron Coderre]

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

[scott45]

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

[Ron Coderre]

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

[scott45]

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

[scott45]

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

[scott45]

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
>
>

[Ron Coderre]

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

[scott45]

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

[scott45]

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