IF function with division and FLOOR function

[faeryluv]

Hi, everyone! I work for a nonprofit and our big Walks are coming up. I am in charge of running reports and getting registration binders ready for volunteers. They will check in Walkers and depending on the amount of money they raised, and the number of members on their team, will hand out t-shirts they have earned. To make it easy for them, I wanted to include a column that determines the amount of t-shirts so there is no thinking required. Volunteers get distracted because there is a lot going on.

While in Google sheets, I have found this formula to work perfect: =if((B2/100)>C2,C2,floor(B2/100))
However, while in Excel, it will not work and I can't figure out why.

Any help will be much appreciated!

Thank you in advance

More Info:

Every registered Walker that raises $100 or more will get a t-shirt. For teams, t-shirts are distributed based on the amount of money they raise, capped by the number of members on the team. For example, a team of 10 that raises $800 would get eight shirts. A team of 10 that raises $1,200 would get 10 shirts.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Team Name[/TD]
[TD]Amount Raised[/TD]
[TD]# Team Members[/TD]
[TD]Shirts Earned[/TD]
[/TR]
[TR]
[TD]Walk It Out[/TD]
[TD]$190.14[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Walkie Talkies[/TD]
[TD]$385.25[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]Walking Warriors[/TD]
[TD]$2,500.06[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[jpvh8]

=if(b2/100>c2,c2,quotient(b2,100))

 

[faeryluv]

Thank you so much!! That works great!!

 

[jpvh8]

Glad I could help. Good luck with the Walk.

 

[shg]

Another way:

=MIN(C2, INT(B2/100))

 

[faeryluv]

Thanks so much!!

 

[faeryluv]

I need help again

I have two tabs/sheets in my workbook - one for individuals and another for teams. Beside each individual I have their team name listed. Next to that column I want a formula that will look up the team amount raised on the second tab and place that amount on the first tab.

Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team Name[/TD]
[TD]Team Raised[/TD]
[TD]Individual[/TD]
[TD]Individual Raised[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Happy Walkers[/TD]
[TD]$FORMULA[/TD]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]$100[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team Name[/TD]
[TD]Team Raised[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Happy Walkers[/TD]
[TD]$1,200[/TD]
[/TR]
</tbody>[/TABLE]

The formula would search sheet 2 for the name "Happy Walkers" and then apply the amount of $1,200 into the column Team Raised on Sheet 1.

I print two reports but the report that lists out each individual walker doesn't also display how much their team raised, only how much the participant raised.

I hope this makes sense.

Thanks for your time!

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">M
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Team Name[/TD]
[TD]Team Raised[/TD]
[TD]Individual[/TD]
[TD]Individual Raised[/TD]
[/TR]
[TR]
[TD]Happy Walkers[/TD]
[TD]$[/TD]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]$100[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2</body>

 

[faeryluv]

I need help again

I have two tabs/sheets in my workbook - one for individuals and another for teams. Beside each individual I have their team name listed. Next to that column I want a formula that will look up the team amount raised on the second tab and place that amount on the first tab.

Sheet 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team Name[/TD]
[TD]Team Raised[/TD]
[TD]Individual[/TD]
[TD]Individual Raised[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Happy Walkers[/TD]
[TD]$FORMULA[/TD]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]$100[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team Name[/TD]
[TD]Team Raised[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Happy Walkers[/TD]
[TD]$1,200[/TD]
[/TR]
</tbody>[/TABLE]

The formula would search sheet 2 for the name "Happy Walkers" and then apply the amount of $1,200 into the column Team Raised on Sheet 1.

I print two reports but the report that lists out each individual walker doesn't also display how much their team raised, only how much the participant raised.

I hope this makes sense.

Thanks for your time!

Oops - please ignore the bottom part of my first post and just read this part. Thanks again!

 

[Michael M]

Try using this in B2....modify the range in red to suit

=VLOOKUP(A2,Sheet2![color=red]$A$2:$B$14[/color],2,0)

 

[faeryluv]

Try using this in B2....modify the range in red to suit

=VLOOKUP(A2,Sheet2![COLOR=red]$A$2:$B$14[/COLOR],2,0)

YOU ARE THE BEST!!! Works perfect!!! Thank you, thank you!!!