Formula Help

[stkin]

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

 

[pgc01]

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]

... 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]

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]

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]

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]

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

 

[stkin]

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]

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)