Solved

excel spreadsheet formula help

Posted on 2012-03-15
14
315 Views
Last Modified: 2012-03-18
I need to create a new spreadsheet based on a rival club's results spreadsheet.
I'm looking at the other club's spreadsheet and I can see a formula, but I don't know where it is getting some of the data from or where it is defined.
screenshotSo, as you can see G3 is the addition of F3 and E3.  But E3 and F3 are numbers that are 1, 2, 3, 4 or 5, and what each value is worth must be defined somewhere else.
Eg:  1st place = 100 points, 2nd = 75 points, 3rd = 50 points, 4th = 25 points and 5th (or 6th/7th/8th etc.) are 10 points.   Each heat judge is worth 10 additional points and so on, to give the final "round" score for the competition.  These are all added at the end of the season and placings are awarded.

Where could the data values of E3 and F3 be defined?  Is there any way to display all formulas or hidden worksheets or something?

Thanks in advance
0
Comment
Question by:Reece Dodds
  • 6
  • 6
  • 2
14 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37727653
This is a user defined function (UDF).

To see this press Alt-F11. On the left of the VBA window there is a pane called Project. There you will see your file as a main entry. This will have Module as a sub entry and there will be one or more further subentries under Module. This function is probably in one of these subentries. Double click the subentry. You will see code. Search for a line which contains

Function competition(...

This is where the function is defined.
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 37727815
ok, cool.

The sheet I want to create is a little more elaborate... see below image.
How do add this "UDF" into my spreadsheet and how do I code it to work like my "Round Points Key"??

new spreadsheet
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37727822
Copy the VBA code for the udf from the other worksheet
Select your new worksheet from the left pane by double clicking
Insert > module
paste your code here.
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 37727840
This is the code from the other spreadsheet...
Option Explicit

Function competition(place, judges) As Integer
    
    Dim score As Integer
    Dim tally As Integer
    
    If place = 1 Then
            score = 100
        ElseIf place = 2 Then
            score = 80
        ElseIf place = 3 Then
            score = 70
        ElseIf place = 4 Then
            score = 60
        ElseIf place = 5 Then
            score = 40
        ElseIf place = 7 Then
            score = 30
        ElseIf place = 9 Then
            score = 20
        ElseIf place = 10 Then
            score = 20
        ElseIf place > 10 Then
            score = 10
        Else
            score = 0
    End If
    If judges = 1 Then
            tally = 5
        ElseIf judges = 2 Then
            tally = 10
        ElseIf judges >= 3 Then
            tally = 15
        Else
            tally = 0
    End If
    
    competition = score + tally
    
End Function

Open in new window

0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 37727961
i've got this so far...
Option Explicit

Function competition(attendance, participation, placed, judged) As Integer

    Dim attended As Integer
    Dim participated As Integer
    Dim score As Integer
    Dim tally As Integer
    
    If attendance = "y" Then
            attended = 10
        Else
            attended = 0
            
    If participation = "y" Then
            participated = 10
        Else
            participated = 0
    
    If place = 1 Then
            score = 100
        ElseIf place = 2 Then
            score = 75
        ElseIf place = 3 Then
            score = 50
        ElseIf place = 4 Then
            score = 25
        ElseIf place = 5 Then
            score = 0
        ElseIf place > 5 Then
            score = 0
        Else
            score = 0
    End If
    If judged = 1 Then
            tally = 5
        ElseIf judged = 2 Then
            tally = 10
        ElseIf judged = 3 Then
            tally = 15
        ElseIf judged = 4 Then
            tally = 20
        ElseIf judged > 4 Then
            tally = 20
        Else
            tally = 0
    End If
    
    competition = attended + participated + score + tally
    
End Function

Open in new window


But I think I'm getting line 1 wrong.  How do I 'declare' attendance when I need it to 'expect' a "y" or a "n"??
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 37728036
I got it working with the following formula, and the original UDF code...
Is there a better way I could be doing it??

using IF's and the UDF
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 37728071
It would be REALLY GREAT if you posted the actual workbook.  Think of one reason why not?  one reason why - it helps us connect to what you're doing more efficiently.  I had to spend 5+ minutes building a mockup, now you've changed the format and ...  See why?
----------------------------------------------------------------
You don't need VBA and a formula will be easier to support and more efficient.  Using your original criteria:

1st place = 100 points, 2nd = 75 points, 3rd = 50 points, 4th = 25 points and 5th (or 6th/7th/8th etc.) are 10 points

And looks like your code is attempting to do that (by the way, you need END IF statements after your ELSE statements!), so for each judge, you get 5, then 10, 15, 20 max for 1,2,3,4,>4 judges

For place, we have the calculation:
IF(ISERROR(VLOOKUP(B3,{1,100;2,75;3,50;4,25;5,10},2)),0,VLOOKUP(B3,{1,100;2,75;3,50;4,25;5,10},2))

if we take the error checking out (if entry is <> 1,2,3,4,5, or > 5), then the formula looks like:
VLOOKUP(B3,{1,100;2,75;3,50;4,25;5,10},2))

So, its a simple vlookup, with a two dimensional array in the formula:

{1,100; etc....  means the first row is 1 in column 1 and 2 in column 2, etc.

For Judge, the calculation is:
IF(ISERROR(VLOOKUP(C3,{1,5;2,10;3,15;4,20},2)),0,VLOOKUP(C3,{1,5;2,10;3,15;4,20},2))

So, for score adding place and judge together, we get:

=IF(ISERROR(VLOOKUP(B3,{1,100;2,75;3,50;4,25;5,10},2)),0,VLOOKUP(B3,{1,100;2,75;3,50;4,25;5,10},2))+IF(ISERROR(VLOOKUP(C3,{1,5;2,10;3,15;4,20},2)),0,VLOOKUP(C3,{1,5;2,10;3,15;4,20},2))

I don't know where your attendance and participation cells are, but let's say they are in each competition block, so the first would be column D and E respectively.  To get the entire score, then we add these to parameters in with an IF statement and then add it all up!

=IF(D3="y",10,0)+IF(E3="y",10,0)+IF(ISERROR(VLOOKUP(B3,{1,100;2,75;3,50;4,25;5,10},2)),0,VLOOKUP(B3,{1,100;2,75;3,50;4,25;5,10},2))+IF(ISERROR(VLOOKUP(C3,{1,5;2,10;3,15;4,20},2)),0,VLOOKUP(C3,{1,5;2,10;3,15;4,20},2))

Now, you can copy that to all the other result cells.

See attached demonstration workbook.

Cheers,

Dave
scoring-r1.xls
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 41

Expert Comment

by:dlmille
ID: 37728079
That formula beats the heck out of 100 lines of code, correct?  ;)

Now that you created tables on your worksheet, you can reference them (rather than embedding the array in the formula, but you'll have to get rid of the equal  sign.

If you upload your workbook in, I can correct that and put the formulas in.  I'll be around for 15 more mins.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37728090
PS - with the scoring rules on the picture you posted, I updated the formulas.  See attached.

Dave
scoring-r2.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37728097
And, here's the final formula with tables in the worksheet like you have.

=IF(D3="y",15,0)+IF(E3="y",15,0)+IF(ISERROR(VLOOKUP(B3,$V$9:$W$13,2)),0,VLOOKUP(B3,$V$9:$W$13,2))+IF(ISERROR(VLOOKUP(C3,$V$16:$W$19,2)),0,VLOOKUP(C3,$V$16:$W$19,2))


See attached.

Dave
scoring-r3.xls
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 37735652
one reason why not - the data in the empty workbook is highly classified and contains billions of dollars worth of financial information... no, but really, it hadn't even occurred to me to attach the spreadsheet.

I have now.

PS.  how do you make it so the heading rows don't move when you scroll down?  And how do you stop people from accidentally erasing or modifying the formula in the cells?
FTBA.xlsm
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37735683
I've incorporated the formula into the first scoring row of your billion dollar enterprise ;)  Now, you don't have to maintain a UDF function which is also more inefficient than the native formula we've created.

>>PS.  how do you make it so the heading rows don't move when you scroll down?  

Use Freeze Panes
http://spreadsheets.about.com/od/exceltools/ss/81027freezepane.htm

>>And how do you stop people from accidentally erasing or modifying the formula in the cells?
The simplest way is to lock the cells you don't want them messing with and then protect the sheet.

If you select all cells, then right click, format cells, protection - the default is locked.  So, once you've confirmed that, you go select all cells that they CAN edit, right click and set protection to not locked (uncheck that).  Then right click your sheet tab and password protect the sheet.
http://www.dummies.com/how-to/content/protecting-cell-data-in-excel-2007.html

See attached formulas incorporated.  I turned your freeze panes on (selected cell B3, then Ribbon->View->Freeze panes) - now you can scroll around and keep the headers at the left/top.  I also unprotected the data entry area on the first 30 rows and password protected your sheet - password "password".

See attached.

Enjoy!

Dave
FTBA.xlsm
0
 
LVL 7

Author Comment

by:Reece Dodds
ID: 37735705
Thanks mate.  You are worth your weight it EE points.
So I was going in the right direction with the formula, but it was a little above my head.
Cheers
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37735712
You were doing the exact right thing, but before you fall back to VBA functions, try to do it with native functions/ask for E-E help on native functions.  That way you know you've exhausted the possibilities and you MUST have a UDF (which should be the last option, because of support issues and inefficiencies, etc.).

Cheers,

Dave
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now