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]

 

[faeryluv]

I can't figure out how to add a photo, but I'll try this link.
This is my sheet:

https://drive.google.com/file/d/0B9A8y3lCwzYwOFBWZnNiT3JVeGNJZlRuVTc3VWxoc01mNUM0/view?usp=sharing

and this is my code I ended up using:

=IF(OR(E16>=100,AND(G16/100>=H16,H16>0)),"yes","no")

 

[Peter_SSs]

OK, so it looks like you don't have the columns I & J as shown in that earlier post. In that case, your formula looks like a good one using the columns you do have.

BTW, for the future, photos are not much good as helpers cannot copy that sample data to their sheets to test with. Better to disguise any sensitive data and post small sample data that can be copied. My signature block below has a link with options for that.

 

[faeryluv]

OK, so it looks like you don't have the columns I & J as shown in that earlier post. In that case, your formula looks like a good one using the columns you do have.

BTW, for the future, photos are not much good as helpers cannot copy that sample data to their sheets to test with. Better to disguise any sensitive data and post small sample data that can be copied. My signature block below has a link with options for that.

Super-awesome, great info!! Thanks so much!!