Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Paul A via OfficeKB.com
 
Posts: n/a
Default Find lowest and highest value from a set of rows

I think I have a complicated problem. Hope somebody can solve this for me

ACC 25 30 29
ACC 24 29 28
ACC 26 28 25
BCC 18 19 17
BCC 17 18 14
BCC 20 21 19

These are 6 rows. Now I would like to find the lowest value in ACC 3 rows
and lowest value in BCC 3 rows. Also a provise for incrementing the rows i
mean if one more ACC rows is insert it has to take that also into account.
Further a chart from low value to high value. I have been trying but with no
luck. If somebody could help me, I would appreciate it. Thanks
  #2   Report Post  
RagDyeR
 
Posts: n/a
Default

Try these *array* formulas:

=MAX(IF(A1:A7="ACC",B1:D7))

=MAX(IF(A1:A7="BCC",B1:D7))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

NOW, you said "insert".
As you insert an entire row, the formulas will revise *automatically* to
include the additional rows.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Paul A via OfficeKB.com" wrote in message
...
I think I have a complicated problem. Hope somebody can solve this for me

ACC 25 30 29
ACC 24 29 28
ACC 26 28 25
BCC 18 19 17
BCC 17 18 14
BCC 20 21 19

These are 6 rows. Now I would like to find the lowest value in ACC 3 rows
and lowest value in BCC 3 rows. Also a provise for incrementing the rows i
mean if one more ACC rows is insert it has to take that also into account.
Further a chart from low value to high value. I have been trying but with
no
luck. If somebody could help me, I would appreciate it. Thanks


  #3   Report Post  
RagDyeR
 
Posts: n/a
Default

You did say "lowest" in your post, but the subject line said lowest *AND*
highest.

So, just change the MAX to MIN, and you'll have them both.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"RagDyeR" wrote in message
...
Try these *array* formulas:

=MAX(IF(A1:A7="ACC",B1:D7))

=MAX(IF(A1:A7="BCC",B1:D7))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

NOW, you said "insert".
As you insert an entire row, the formulas will revise *automatically* to
include the additional rows.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Paul A via OfficeKB.com" wrote in message
...
I think I have a complicated problem. Hope somebody can solve this for me

ACC 25 30 29
ACC 24 29 28
ACC 26 28 25
BCC 18 19 17
BCC 17 18 14
BCC 20 21 19

These are 6 rows. Now I would like to find the lowest value in ACC 3 rows
and lowest value in BCC 3 rows. Also a provise for incrementing the rows i
mean if one more ACC rows is insert it has to take that also into account.
Further a chart from low value to high value. I have been trying but with
no
luck. If somebody could help me, I would appreciate it. Thanks



  #4   Report Post  
Paul A via OfficeKB.com
 
Posts: n/a
Default


Thank you RagDyR

RagDyeR wrote:
You did say "lowest" in your post, but the subject line said lowest *AND*
highest.

So, just change the MAX to MIN, and you'll have them both.
Try these *array* formulas:

=MAX(IF(A1:A7="ACC",B1:D7))

=MAX(IF(A1:A7="BCC",B1:D7))



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1
  #5   Report Post  
Paul A via OfficeKB.com
 
Posts: n/a
Default


I have tried the following but it shows and #Value Error. Can you let me
know why this occurs

=MAX(IF(A1:A3="BCC",B1:B3))


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1


  #6   Report Post  
RagDyeR
 
Posts: n/a
Default

Did you enter the formula using CSE?

I repeat:
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

I should add, that even after a revision of the formula, you must use CSE!

An array formula must *end up* being enclosed in curly brackets.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Paul A via OfficeKB.com" wrote in message
...

I have tried the following but it shows and #Value Error. Can you let me
know why this occurs

=MAX(IF(A1:A3="BCC",B1:B3))


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find min/max in 2 combined columns Tammi Excel Worksheet Functions 3 June 22nd 05 08:19 PM
How do i sort a group of cells from highest to lowest JulieR Excel Discussion (Misc queries) 2 March 8th 05 09:31 PM
Formula to sum and list highest to lowest Brian Excel Worksheet Functions 1 December 11th 04 11:26 PM
How to find highest, lowest and last cell in row? Sam Excel Discussion (Misc queries) 3 December 3rd 04 12:59 PM
How to find highest, lowest and last cell in a row? Sam Excel Worksheet Functions 1 December 3rd 04 12:51 PM


All times are GMT +1. The time now is 09:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"