Lowest/Highest Number Cells

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
Hello,

I have a data listed from A1 to G1. What I want to do is

Check out all the numbers written in those cells, take the highest number among and write to the following cell (lets say A3)

also

Check out all the numbers written in those cells, take the lowest number among and write to the following cell (lets say A4)

Thank you in advance

Audrey
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Like this perhaps

<b>Loco Keys</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">21</td><td style="text-align:right; ">9</td><td style="text-align:right; ">90</td><td style="text-align:right; ">76</td><td style="text-align:right; ">64</td><td style="text-align:right; ">4</td><td style="text-align:right; ">34</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">90</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A3</td><td >=MAX(A1:G1)</td></tr><tr><td >A4</td><td >=MIN(A1:G1)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 

audrey

Active Member
Joined
Jul 30, 2008
Messages
491

ADVERTISEMENT

thanks you guys
 

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
what if I have month on A1:G1 and values under B1:G1 so basically check out the numbers among B1:G1 and when lowest or highest, write the month but not the number.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

That sounds like this might work (assumes month names in A1:G1 and values in A2:G2 - I am afraid I was finding it hard to discern the layout of your data)

=INDEX($A$1:$G$1,MATCH(MAX($A$2:$G$2),$A$2:$G$2,0))

Replace Max with Min for the other.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

<b>Loco Keys</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-style:eek:utset;border-color:#f0f0f0; ">G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >Jan</td><td >Feb</td><td >Mar</td><td >Apr</td><td >May</td><td >Jun</td><td >Jul</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">21</td><td style="text-align:right; ">9</td><td style="text-align:right; ">90</td><td style="text-align:right; ">76</td><td style="text-align:right; ">64</td><td style="text-align:right; ">4</td><td style="text-align:right; ">34</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >Mar</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td >Jun</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A4</td><td >=INDEX(A1:G1,MATCH<span style=' color:008000; '>(MAX<span style=' color:#0000ff; '>(A2:G2)</span>,A2:G2,0)</span>)</td></tr><tr><td >A5</td><td >=INDEX(A1:G1,MATCH<span style=' color:008000; '>(MIN<span style=' color:#0000ff; '>(A2:G2)</span>,A2:G2,0)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 

audrey

Active Member
Joined
Jul 30, 2008
Messages
491
One more!

Highest A3
Second Highest A4
Third Highest A5

Lowest B3
Second Lowest B4
Third Lowest B5
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top