Displaying a Range

M

ManOSteel

New Member
Joined
Jan 31, 2012
Messages
14
Hey all, trying to display a range of ounces for various laundry detergent but my formula has broke. Anyone see where im going wrong here? I'd like to have it display like this: 45 - 50 oz in cell F21.Thanks in advance...

=IFERROR(IF(MIN(IF(B:B=B17,IF(A:A=" ",G:G)))=MAX(IF(B:B=B17,IF(A:A=" ",G:G))),MIN(IF(B:B=B17,IF(A:A="",G:G)))&" "&H18,MIN(IF(B:B=B17,IF(A:A=" ",G:G)))&" - "&MAX(IF(B:B=B17,IF(A:A=" ",G:G)))&" "&H18),"")

dateposted-public
dateposted-public
dateposted-public
[B]Excel 2010[/B][TABLE]
<tbody>[TR]
[TH][/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]ARM & HAMMER BAKING SODA 16 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]16 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]ARM & HAMMER BAKING SODA FRIDGE-N-FREEZER 16 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]16 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: center"]0 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]ARM & HAMMER DTRNGT LNDRY LIQ OXICLN FRSH 45 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]45 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]ARM & HAMMER LIQ LAUNDRY 2X CONC SENS SKIN 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]ARM & HAMMER DTRNGT LNDRY LIQ LVNDR/LN CNCT 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD]ARM & HAMMER LQ DET 2X CN CLEAN BURST 32LD 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD]ARM & HAMMER LQ DT 2X PURFUME/DYE FREE 32LD 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD]ARM & HAMMER DTRNGT LNDRY LIQ ORCH BLM 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD]ARM & HAMMER LQ DT 2X CLN BRST W/BLCH 26LD 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD]ARM & HAMMER DETERGENT LIQ BIOENZYM BOT SPR 43.75 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]43.75[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]43.75 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Ad Analysis- Core Mailer UP[/B][/COLOR][/CENTER]

 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Can you post the data for columns A:B as well? Your formula references them. Also, what's in B17 and H18 and why are they referenced on row 21?

WBD
 
Upvote 0
[B]Excel 2010[/B][TABLE]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320000020[/TD]
[TD]ARM & HAMMER BAKING SODA FRIDGE N FREEZER 14 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]14 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320001110[/TD]
[TD]ARM & HAMMER BAKING SODA 16 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]16 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320001155[/TD]
[TD]ARM & HAMMER BAKING SODA FRIDGE-N-FREEZER 16 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]16 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]ZQM 11/13 Arm and Hammer Liquid Detergent 50 z[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"][/TD]
[TD="align: center"]0 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320009045[/TD]
[TD]ARM & HAMMER DTRNGT LNDRY LIQ OXICLN FRSH 45 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]45 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320009140[/TD]
[TD]ARM & HAMMER LIQ LAUNDRY 2X CONC SENS SKIN 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320009954[/TD]
[TD]ARM & HAMMER DTRNGT LNDRY LIQ LVNDR/LN CNCT 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320009990[/TD]
[TD]ARM & HAMMER LQ DET 2X CN CLEAN BURST 32LD 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320009991[/TD]
[TD]ARM & HAMMER LQ DT 2X PURFUME/DYE FREE 32LD 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320009994[/TD]
[TD]ARM & HAMMER DTRNGT LNDRY LIQ ORCH BLM 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320009995[/TD]
[TD]ARM & HAMMER LQ DT 2X CLN BRST W/BLCH 26LD 50 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]50 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3320097230[/TD]
[TD]ARM & HAMMER DETERGENT LIQ BIOENZYM BOT SPR 43.75 OZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]43.75[/TD]
[TD="align: center"]OZ[/TD]
[TD="align: center"]43.75 OZ[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]YES MAILER ASPARAGUS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Ad Analysis- Core Mailer UP[/B][/COLOR][/CENTER]

 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
415
MrDB4Excel
MrDB4Excel
W
  • Solved
Removing Data Validation List Items Once Selected In Another Cell
Replies
3
Views
411
James006
James006
A
  • Question
Problem With Scrolling Data Through A Static Display Range
Replies
5
Views
943
Ark68
A

Forum statistics

Threads
1,215,235
Messages
6,123,782
Members
449,123
Latest member
StorageQueen24

Share this page

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