Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find min/max in 2 combined columns | Excel Worksheet Functions | |||
How do i sort a group of cells from highest to lowest | Excel Discussion (Misc queries) | |||
Formula to sum and list highest to lowest | Excel Worksheet Functions | |||
How to find highest, lowest and last cell in row? | Excel Discussion (Misc queries) | |||
How to find highest, lowest and last cell in a row? | Excel Worksheet Functions |