Formula Help

stkin

Board Regular
Joined
Dec 18, 2013
Messages
52
I have a formula that checks for the lowest number in a column, some times one of the cells will be blank, when it is blank the formula makes this cell the lowest number, here is the formula

=IF(COUNTIF(C25:C28,MIN(C25:C28))=1,INDEX($A$25:$A$28,MATCH(MIN(C25:C28),C25:C28,0),0),D14)

can I add something in this or change something to not look at the blank cell.

Thanks

Tommy
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi Tommy

Try:

=IF(COUNTIF(C25:C28,MIN(IF(C25:C28<>"",C25:C28)))=1,INDEX($A$25:$A$28,MATCH(MIN(IF(C25:C28<>"",C25:C28)),C25:C28,0),0),D14)

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
... or, non-array

=IF(COUNTIF(C25:C28,LARGE(C25:C28,COUNT(C25:C28)))=1,INDEX($A$25:$A$28,MATCH(LARGE(C25:C28,COUNT(C25:C28)),C25:C28,0),0),D14)
 

stkin

Board Regular
Joined
Dec 18, 2013
Messages
52
thank you, both work except for one problem, when I enter the formula it makes the first column return 0 and not the correct answer all the other cells when I copy forward work perfect, any help

... or, non-array

=IF(COUNTIF(C25:C28,LARGE(C25:C28,COUNT(C25:C28)))=1,INDEX($A$25:$A$28,MATCH(LARGE(C25:C28,COUNT(C25:C28)),C25:C28,0),0),D14)
 

stkin

Board Regular
Joined
Dec 18, 2013
Messages
52

ADVERTISEMENT

pgc01

Thanks, I can't tell you what happened if I used the array formula it works, and if I use the non-array it doesn't work. So it is working and again thanks for all your help.
 

stkin

Board Regular
Joined
Dec 18, 2013
Messages
52
pgc01

here is a question for you if this is possible, I have four people in each group. So lets say this is the winnings after playing

Bill 3.25 Ok, hope I can explain this right, Bill gets 2.75 from Bob, 2.50 from Dave and 2.25 from Terry. But also Dave gets .25 from Bob and then Terry gets .50 from Bob and .25 from Terry. So is there a formula I can you
Bob .50 that will do all that math as the game is played. Also the order of the results will be different every time. What I mean is the last person could win the most or it could be the second or third, I know way of knowing
Dave .75 who is going to win.
Terry 1.00 So this is what I'm trying to accomplish, Bob would pay Bill 3.50, Dave would pay Bill 2.25 and Terry would pay bill 1.75. Then Bob would pay Dave nothing, and owe Terry .50. and Dave would owe Terry .25. I hope
I hope I explained this well enough to understand. So I asking for help and if it can be done.

Thanks

Tommy
 

stkin

Board Regular
Joined
Dec 18, 2013
Messages
52

ADVERTISEMENT

pgc01

here is a question for you if this is possible, I have four people in each group. So lets say this is the winnings after playing from one group

Bill 3.25
Bob .50
Dave .75
Terry 1.00



Ok, hope I can explain this right, Bill gets 2.75 from Bob, 2.50 from Dave and 2.25 from Terry. But also Dave gets .25 from Bob and then Terry gets .50 from Bob and .25 from Terry. So is there a formula I can use that will do all that math as the game is played. Also the order of the results will be different every time. What I mean is the last person could win the most or it could be the second or third, I have know way of knowing who is going to win. So this is what I'm trying to accomplish, Bob would pay Bill 3.50, Dave would pay Bill 2.50 and Terry would pay bill 1.50. Then Bob would pay Dave nothing, and Terry nothing and Dave would owe Terry .25. So to explain this Bob paid Dave and Terry by paying Bill what he lost to the others and then reduced what the other owned Bill. I hope I explained this well enough to understand. So Im asking for help and if it can be done.

Thanks

Tommy
 
Last edited:

stkin

Board Regular
Joined
Dec 18, 2013
Messages
52
pgc01

The formula you gave me is great. I want to know if it is possible to use this formula but show 0 or blank in the cell until I enter a number in cells C25:C28. If you don't know this is a golf formula to show skins winners with carryovers. So when I enter the handicap it puts the highest handicapper as winner until the hole is played, so when someone ask me what the skin count is, it is off because of that. If you need me to send you the entire app I will just email and I will include it. Here is my stkinatmacdotcom

Thanks
 

stkin

Board Regular
Joined
Dec 18, 2013
Messages
52
pgc01

The formula you gave me is great. I want to know if it is possible to use this formula but show 0 or blank in the cell until I enter a number in cells C25:C28. If you don't know this is a golf formula to show skins winners with carryovers. So when I enter the handicap it puts the highest handicapper as winner until the hole is played, so when someone ask me what the skin count is, it is off because of that. If you need me to send you the entire app I will just email and I will include it. Here is my stkinatmacdotcom

Thanks

... or, non-array

=IF(COUNTIF(C25:C28,LARGE(C25:C28,COUNT(C25:C28)))=1,INDEX($A$25:$A$28,MATCH(LARGE(C25:C28,COUNT(C25:C28)),C25:C28,0),0),D14)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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