MS Excel assistance

[akkaps]

I need help trying to use a formula on Excel.

I cannot seem to be able to round prices to the nearest $100. I am able to round it to the nearest dollar using the =ROUND( equation.

Does anyone know how to do this?

 

[red+black]

round up to nearest 100 = CEILING(A1,100)
round down to nearest 100 = FLOOR(A1,100)
round up or down to nearest 100 = MROUND(A1,100)

 

[superfraser]

=MROUND(cellToRound, 100)

This will round your cell to the nearest multiple of 100.

e.g. 149 will be rounded to 100 and 150 will be rounded to 200.

 

[akkaps]

Thanks guys. You've been a real help.

 

[akkaps]

This is a bit more difficult, I am trying to create an AFL Ladder on Excel. I already have the fixture. Any help?

 

[devil demon]

Without knowing too much what you want to do. You're going to need to learn how to use pivot tables

 

[red+black]

Search google for FIFA World Cup spreadsheets. I've seen some that have the full draw, you plug in the results, each group's table is updated, knockout stage matches are populated. I reckon that's pretty similar to what you're trying to do.

 

[akkaps]

I have googled FIFA WC spreadsheets, and have tried to change a few from read-only to unprotected, but they keep asking me for a password.

 

[seanoff]

umm this is quite complex. the solution below is me trying to avoid long formulae. much easier to edit and understand.

The Ladder = Sheet 1
with team name, points, won, lost, drawn, pf, pa, percentage.

this gets data from the team sheets. the sum of all 22 games

then sort the ladder using points as the first sort and percentage as the secondary sort range. if you want to automate this learn VB.



i'd have a sheet for each team with all those columns and a row for each game.
so the Gold Coast would have 22 rows like this

pf | pa | points | win | loss | draw | percentage
=(Rd1$b$1) =(Rd1$i$1) etc don't bother with the percentage column for each game.


then at the bottom of the 22 rows.

@sum(1,22) etc etc etc =pf/pa (this is the data dragged forward to the ladder spreadsheet)

each team would drag data forward from the fixture sheets, which would require at least points for and against. and some function to calculate win/loss/draw and the points for each result.

in this example columns B and I are points scored, C and J are points awarded, D and K are win counters, E and L loss counter and F and M draw counters.

D would be =If(B1>I1, 1,0)
so
Rd 1
A B C D E F H I J K L M
Gold Coast | 100 | 4 | 1 | 0 |0 Richmond | 60 | 0 | 0 | 0 | 0

 

[SnappyHappy]

Hey guys,

I'm also looking for some help with MS Excel. I'm trying to set it up for my Brownlow Count. Is there any way I can set it up so that when I enter the votes for each game, it automatically updates the leaderboard?? Cheers!

 

[MarcusP2]

Sorted this yet? Seems like you could use a sumif to generate totals per player, but not sure about the auto sorting. Pivot table could also do it I think.

Sent from my LT26i using Tapatalk

 

[akkaps]

Never understood =sumif