# Formula Help

#### stkin

##### Board Regular
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### pgc01

##### MrExcel MVP
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
... 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
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

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

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
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
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)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,272
Messages
5,836,326
Members
430,420
Latest member
Sensen

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

### Which adblocker are you using?

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

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