Reece

asked on 3/15/2012

excel spreadsheet formula help

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.

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

Microsoft Excel

Last Comment

dlmille

8/22/2022 - Mon

Saqib Husain

3/15/2012

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.

Reece

3/15/2012

ASKER

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

Saqib Husain

3/15/2012

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.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Reece

3/15/2012

ASKER

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

Reece

3/15/2012

ASKER

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

Reece

3/15/2012

ASKER

I got it working with the following formula, and the original UDF code...
Is there a better way I could be doing it??

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

SOLUTION

dlmille

3/15/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

dlmille

3/15/2012

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

dlmille

3/15/2012

PS - with the scoring rules on the picture you posted, I updated the formulas. See attached.

Dave
scoring-r2.xls

dlmille

3/15/2012

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Reece

3/18/2012

ASKER

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

ASKER CERTIFIED SOLUTION

dlmille

3/18/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Reece

3/18/2012

ASKER

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

dlmille

3/18/2012

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.