Minimum, excl zero - non-array/range

M

Marelize

New Member
Joined
Apr 5, 2023
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
Good day
I want to obtain the lowest price for certain products from different suppliers. And then decide which company has the overall lowest prices. The data is not in a range, but in various columns in one row.
In this mini sheet it is the highlighted columns.

Column AG and AH refers.
In AG I want to have the minimum price but without giving me zero as a minimum, while in AH I would like to get the company name as return (Z1, S1, L1).

This I accomplished by using MIN-function (but it returns zero too) and then using MATCH to get the company names.
But how do I eliminate the zero as a return...?

First time poster. English second language.

Thank you
Marelize

Soymaster chemies.xlsx
LMNOPQRSTUVWXYZAAABACADAEAFAGAH
2Prys per verpakking (kan / boks)Aankoopkoste vir seisoenRand per eenheidRand per hektaarGebruikte koste per seisoenPrys per verpakkingAankoopkoste vir seisoenRand per eenheid per Rand per hektaarGebruikte koste per seisoenPrys per verpakkingAankoopkoste vir seisoenRand per eenheid per Rand per hektaarGebruikte koste per seisoenLowest Price / Laagste Prys lowest price company's name
3R1,590.00R6,360.00R79.50/ lt R11.93R5,962.50R2,100.00R8,400.00R105.00/ lt R15.75R7,875.00R800.00R3,200.00R40.00/ lt R6.00R3,000.00R3,200.00Farm-Agri
4R2,342.86R4,685.72R117.14/ lt R9.37R4,685.72R2,500.00R5,000.00R125.00/ lt R10.00R5,000.00R7,500.00R15,000.00R375.00/ lt R30.00R15,000.00R4,685.72Oos-Vaal
5R3,720.00R14,880.00R372.00/ lt R48.36R12,090.00R2,950.00R11,800.00R295.00/ lt R38.35R9,587.50  / lt   Farm-Agri
6R1,300.00R5,200.00R65.00/ kg R16.25R4,062.50R1,150.00R4,600.00R57.50/ kg R14.38R3,593.75  / kg     
7R1,800.00R10,800.00R360.00/ lt R39.60R9,900.00R1,200.00R7,200.00R240.00/ lt R26.40R6,600.00R1,950.00R11,700.00R390.00/ lt R42.90R10,725.00R7,200.00Farmer's Agri-Care
Oos-Vaal
Cell Formulas
RangeFormula
M3:M7M3=IFERROR(K3*L3,0)
N3:N7N3=IFERROR(L3/D3,0)
P3:P7P3=E3
Q3:Q7Q3=IFERROR(F3/1000*N3,0)
R3:R7R3=IFERROR(Q3*I3*H3,0)
T3:T7T3=S3*K3
U3:U7U3=IFERROR(S3/D3,0)
X3:X7X3=F3/1000*U3
Y3:Y7Y3=X3*I3*H3
AA3:AA7AA3=Z3*K3
AB3:AB7AB3=IFERROR(Z3/D3,0)
AE3:AE7AE3=F3/1000*AB3
AF3:AF7AF3=AE3*H3*I3
AG6:AG7,AG3:AG4AG3=IF(MIN(AA3,T3,M3)=0,"",(MIN(AA3,T3,M3)))
AH3AH3=IF(MATCH(AG3,L3:AF3,0)=16,$Z$1,IF(MATCH(AG3,L3:AF3,0)=9,$S$1,IF(MATCH(AG3,L3:AF3,0)=2,$L$1)))
AH4:AH7AH4=IFERROR(IF(MATCH(AG4,L4:AF4,0)=16,$Z$1,IF(MATCH(AG4,L4:AF4,0)=9,$S$1,IF(MATCH(AG4,L4:AF4,0)=2,$L$1))),0)
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi & welcome to MrExcel.
Can you re-post the data from col D onwards.
 
Upvote 0
Did a quick translation addition to title row. Hope it makes sense

Soymaster chemies.xlsx
DEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1Oos-VaalFarmer's Agri-CareFarm-Agri
2Produkverpakking (kan / boks)lt, ml, kgused / ha Aanwending / haTotal hectares Aantal hektareSpray count Aantal bespuitingsTotal Spray hoeveelheid vir hektare (in lt/kg)Quantity units Aantal kanne / bokse te koopPrice per unite Prys per verpakking (kan / boks)Cost buyed for season Aankoopkoste vir seisoenPrice per lt / kg Rand per eenheidPrice per hectare Rand per hektaarCost used Gebruikte koste per seisoenPrys per verpakkingAankoopkoste vir seisoenRand per eenheid per Rand per hektaarGebruikte koste per seisoenPrys per verpakkingAankoopkoste vir seisoenRand per eenheid per Rand per hektaarGebruikte koste per seisoenLowest Price / Laagste Prys lowest price company's name
320lt150ml2502754R1,590.00R6,360.00R79.50/ lt R11.93R5,962.50R2,100.00R8,400.00R105.00/ lt R15.75R7,875.00R800.00R3,200.00R40.00/ lt R6.00R3,000.00R3,200.00Farm-Agri
420lt80ml2502402R2,342.86R4,685.72R117.14/ lt R9.37R4,685.72R2,500.00R5,000.00R125.00/ lt R10.00R5,000.00R7,500.00R15,000.00R375.00/ lt R30.00R15,000.00R4,685.72Oos-Vaal
510lt130ml250132.54R3,720.00R14,880.00R372.00/ lt R48.36R12,090.00R2,950.00R11,800.00R295.00/ lt R38.35R9,587.50  / lt   Farm-Agri
620kg250g250162.54R1,300.00R5,200.00R65.00/ kg R16.25R4,062.50R1,150.00R4,600.00R57.50/ kg R14.38R3,593.75  / kg     
75lt110ml250127.56R1,800.00R10,800.00R360.00/ lt R39.60R9,900.00R1,200.00R7,200.00R240.00/ lt R26.40R6,600.00R1,950.00R11,700.00R390.00/ lt R42.90R10,725.00R7,200.00Farmer's Agri-Care
820lt1000ml250375038R800.00R30,400.00R40.00/ lt R40.00R30,000.00  /    /    
9    /    /    /    
Oos-Vaal
Cell Formulas
RangeFormula
J3:J9J3=(F3*H3*I3)/1000
K3:K9K3=IFERROR(ROUNDUP(J3/D3,0),0)
M3:M9M3=IFERROR(K3*L3,0)
N3:N9N3=IFERROR(L3/D3,0)
Q3:Q9Q3=IFERROR(F3/1000*N3,0)
R3:R9R3=IFERROR(Q3*I3*H3,0)
T3:T9T3=S3*K3
U3:U9U3=IFERROR(S3/D3,0)
X3:X9X3=F3/1000*U3
Y3:Y9Y3=X3*I3*H3
AA3:AA9AA3=Z3*K3
AB3:AB9AB3=IFERROR(Z3/D3,0)
AE3:AE9AE3=F3/1000*AB3
AF3:AF9AF3=AE3*H3*I3
AG6:AG9,AG3:AG4AG3=IF(MIN(AA3,T3,M3)=0,"",(MIN(AA3,T3,M3)))
AH3AH3=IF(MATCH(AG3,L3:AF3,0)=16,$Z$1,IF(MATCH(AG3,L3:AF3,0)=9,$S$1,IF(MATCH(AG3,L3:AF3,0)=2,$L$1)))
AH4:AH9AH4=IFERROR(IF(MATCH(AG4,L4:AF4,0)=16,$Z$1,IF(MATCH(AG4,L4:AF4,0)=9,$S$1,IF(MATCH(AG4,L4:AF4,0)=2,$L$1))),0)
P3:P8P3=E3
 
Upvote 0
Thanks for that.
How about
Excel Formula: 
=IF(AG3="","",IFERROR(IF(MATCH(AG3,L3:AF3,0)=16,$Z$1,IF(MATCH(AG3,L3:AF3,0)=9,$S$1,IF(MATCH(AG3,L3:AF3,0)=2,$L$1))),0))
 
Upvote 0
Fluff said:
Thanks for that.
How about
Excel Formula: 
=IF(AG3="","",IFERROR(IF(MATCH(AG3,L3:AF3,0)=16,$Z$1,IF(MATCH(AG3,L3:AF3,0)=9,$S$1,IF(MATCH(AG3,L3:AF3,0)=2,$L$1))),0))
Click to expand...
Thanks
But I still need the AG column to not return zero as a minimum.
Example: Supplier 3 doesn't carry product ABC, so his price for this would remain empty "zero". Excel picks this then as the minimum instead of omitting the zero and taking second lowest price as minimum.

Thus how can I get it to not use zero as a minimum...?
 
Upvote 0
I realize that, but I want the lowest price for a product, not a zero or empty price.

Eg I buy butter for $10 at store ABC, $12 at store XYZ but store DDD doesn't stock butter. I want to determine the lowest price, not the zero (non-stockist) as the minimum... :unsure: And because my data is not in a range / array (all together like in a column or a row) the function "SMALL", which would be perfect, doesn't work...
Maybe I should rather look at a database type program and not use Excel for this?
 
Upvote 0
Ok, how about
Excel Formula: 
=IFERROR(1/(1/AGGREGATE(15,6,M3:AA3/(M3:AA3<>0)/(MOD(COLUMN(M3:AA3),7)=6),1)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Average Function
Replies
8
Views
381
Peter_SSs
Peter_SSs
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor
L
  • Question Question
Excel formula not carrying down correctly, using wrong intervals of rows?
Replies
4
Views
746
bebo021999
B
R
  • Solved
SUMIF VBA ARRAY & DICTIONARY
2 3
Replies
23
Views
4K
roykana
R
H
  • Question Question
Complicated Calculation
Replies
6
Views
2K
Eric W
Eric W

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

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