You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- min and if statement

Advertisement

QUESTION: I have 4 columns of golf score totals. 43, 41, 60, 26 in cells b20, c20, d20, e20 respectively. In cell row 21 I am to show "1" in the winner's cell and "0" in the other three cells#

It should end up looking like this, Cell B21=0, C21=0, D21=0 and the winner #the lowest score for golf# E20=1

Thank you for your help.

ANSWER: Patricia,

in B21 put this formula

=if(B$20=Min($B$20:$E$20),1,0)

Then drag fill this formula to the right to fill in C21:E21

If there is a tie and two entries match the lowest score, then it would mark both.

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Tom, thank you for your help earlier today. This pertains to the same workbook.

Overview:

I have a total of 6 Worksheets - Worksheets 1 through 5 have scores for Miniature Golf Games 1 - 5. Worksheet 6 - is the Summary Sheet.

On all worksheets - Column A list the 18 rounds played. B1:E1 has the 4 player’s name, and B2:E19 has the round points. In addition, on the Game Worksheet - Row 20 are the total points (B19:E19), and on Row 21 the winner is identified with (1) and losers with a (0). (I used the formula you gave me here.)

Here is what I am trying to do on the Summary Worksheet:

Step 1: Enter 3D Formulas on the Summary Sheet to calculate the average points per round in range B2:E19.

Step 2: Enter Average Total Points on Row 20.

Step 3: Enter Total Points Per Player on Row 21.

Step 4: Enter Number of Games Won by each player on Row 22.

Thank you for your help again.

Patricia

Patricia,

assume that the sheet names are

Game1

Game2

Game3

Game4

Game5

Summary

assume at least the Game sheets are in that order in the tab order then you would go to B2 of Summary and put in

=AVERAGE(Game1:Game5!B2:E2)

now copy the formula down to B19.

That should give you the average points per round across five games. If that isn't what you want (for step1), you need give me more information on what you do want.

a possible alternate interpretation is to put =Average(Game1:Game5!B2)

drag fill that across to E2. Select B2:E2 and drag fill down to B19:E19

Step 2. I will assume you want average total points for each player across five games since you said you wanted the answer in row 20 rather than in B20

in B20 put in =Average(Game1:Game5!B20)

then drag fill across to E20

Step 3. in B21 put in the formula =Sum(Game1:Game5!B20) then drag fill across to E21

step 4. in B22 put in the formula =Sum(Game1:Game5!B21) then drag fill across to E22

Again, it isn't totally obvious to me exactly what you want. I have provided formulas that should provide what I think you want.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Tom, thank you very much. Sorry, I gave you some wrong information for Step 3, but I figured it out. Your help was greatly appreciated. Patricia |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.