Just going to park a little thread here..

Not for work or anything, just to improve my general skills at Excel, I have decided to make a spreadsheet to calculate a season football league ladder.

I started out calculating the margin on any game, and using the IF function to figure out if it was a win or loss based on whether the margin was >0 or not.

Then I hit a snag, what if it was a draw and the margin was actually zero?

Is there any sort of three-way logic function where I can specify if margin=positive then W, margin=negative then L, and if margin=0 then D

Sorry if this is a stupid question…. but there will be many more.

split it like

if (x > 0) { // do stuff
} else if (x == 0) { // do stuff
} else { // do stuff
}

=IF(A1>0,“positive”,IF(A1<0,“negative”,“zero”)

)

(

sibeen said:

(

Stop messing up the SCIENTIFIC brackets sibeen.

)

The Rev Dodgson said:

sibeen said:

(

Stop messing up the SCIENTIFIC brackets sibeen.

)

Yes. Not closing your tags is just rude /*

You can also use a lookup table:

Put
-9.9E307 negative
0 zero
2.23E-308 positive

in cells A1 to B3, then if your number is in C1:
=VLOOKUP
will return negative, zero or positive.

This option is useful when you have more than three options, when multiple nested Ifs become confusing.

It also allows you to put a range on values that will be treated as zero:

-9.9E307 negative
-1 zero
1 positive

will return zero for anything greater or equal to -1 and less than 1

Actually:
=VLOOKUP (C1, A1:B3,2)

will work better.

Beauty, thanks Rev. I’ll have a play with it now.

party_pants said:

Beauty, thanks Rev. I’ll have a play with it now.

Stop when you need glasses

That worked beautifully…

but what do these bits:

-9.9E307

2.23E-308

actually mean?

It worked, just asking for a bit more understanding.

Fill formating. locking the cell range?

- told ya there were lots of stupid questions…

I’ve got a SUMIF formula to scan through a column and add up only the points for Adelaide (being first in alpha order).
Next row is Brisbane, I want to copy the formula to scan through the same columns but look for Brisbane. Then in turn Carlton, Collingwood and so on for each team.

I tried to fill the formula by dragging down, but it shifts the Range and Sum_Range by +1 each time.

I want to lock the Range and Sum_Range and have fill formating only adjust the Criteria.

Is there a way to do it?