Fantasy Football Spreadsheet

[CChiz5]

Hey guys,

I have a question regarding a fantasy football calculation.

Im doing the stats for our league on excel and cant figure out how to add in a bonus for a certain amount of yards gained.

I have the formula correct that gives 1 point for every 25 passing yards, at 300 yards we get a 5 point bonus.

So 300 yards would equal 17 with the bonus. How can I import this bonus into the same formula without having to create another column.

Thanks

 

[CChiz5]

anyone?

 

[Sal Paradise]

=ROUNDUP(Yards/25,1)+IF(Yards>300,5,0)

Assuming you want to round up your yards points.

Change "Yards" to whatever Cell your yards are in.

If you want 5 points for 300 yards, change it to
=ROUNDUP(Yards/25,1)+IF(Yards>=300,5,0)

Otherwise it will give 5 points from 301 yards (or 300.1 yards).

 

[CChiz5]

thanks so much

 

[CChiz5]

if i wanted to add 5 more points for 400 yards would i just do the same thing and instead of 300 add another one listing 400?

 

[Sal Paradise]

Bingo.

=ROUNDUP(Yards/25,1)+IF(Yards>=300,5,0)+IF(Yards>=400,5,0)

 

[CChiz5]

Im having an issue with the math before the bonus amounts.

Its 1 point for every 25 yards.

How come when I enter 20 yards it still gives it 1 point, when in reality it should say 0.

here is my current formula.

=IF(H7>=300,(5),(0))+IF(H7>=400,(5),(0))+H7/25

 

[CChiz5]

anyone?

 

[CChiz5]

help please

 

[Sal Paradise]

Because 20/25 = .8 which rounds up to 1.

If you want fractional values to be lower, change to:
=IF(H7>=300,(5),(0))+IF(H7>=400,(5),(0)+ROUNDDOWN(H7/25,0)