I want in A1 to find minimum SUM if no minimum number in row.
Here is example attached:
Book1.xls
I want in A1 to find minimum SUM if no minimum number in row.
Here is example attached:
Book1.xls
Last edited by zbor; 12-25-2009 at 05:03 AM.
Never use Merged Cells in Excel
How it is? i thought minimum number always exist. For example u have thousand and thousand numbers, can't u get min number amogst them?
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, ?Born in USSR?
Vusal M Dadashev
Baku, Azerbaijan
Minimum sum is 6 but it contains minimum number (1) so minimum sum is 15![]()
Please Login or Register to view this content.
think I understand
try this one
EDIT![]()
Please Login or Register to view this content.
I REALLY SAW AFTER POSTING
Very funny
Thank for greeting
Last edited by contaminated; 12-24-2009 at 02:28 PM.
Someone else?
In testing the formula by ContaminatedWithExcel there are instances where it returns a result although the minimum number is in the same row as the result. Not what zbor is looking for.
I admit it's not a pretty formula given the use of volatile functions , but it seems to be working as far as I can tell. I resorted to the use of helper cells, of which the formulas you may want to consolidate into a single formula.
=IF(OFFSET(INDIRECT(ADDRESS(MATCH(MIN($L$3:$L$12),$L$3:$L$12,0)+2,12)),0,4,1,1)=FALSE,SMALL($L$3:$L$12,1),SMALL($L$3:$L$12,2))
EDIT: After further testing (repeatedly forcing recalculation), my solution also returns an occasional incorrect result, albeit much fewer instances.
Last edited by Palmetto; 12-24-2009 at 07:48 PM. Reason: Add comment about formula results
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Hey zbor, I like the hidden message.......
You can use this array formula
=MIN(IF(COUNTIF(OFFSET(C3:K3,ROW(C3:K12)-ROW(C3),0),M3),"",L3:L12))
...or this one.....
=MIN(IF(MMULT((C3:K12=M3)+0,TRANSPOSE((COLUMN(C3:K3)>1)+0))=0,L3:L12))
Last edited by daddylonglegs; 12-24-2009 at 08:15 PM.
god! there will be always a minimum value. if you are good enough to input the same value in all the cells in arow
Try this
Committed with Ctrl+Shift+Enter.![]()
Please Login or Register to view this content.
You can also see attached file.
Hope this help,
WindKnife
Thanks to fastest. They got their reward![]()
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks