# IF function with division and FLOOR function

#### faeryluv

##### New Member
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!

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.

 Team Name Amount Raised # Team Members Shirts Earned Walk It Out \$190.14 2 1 Walkie Talkies \$385.25 5 3 Walking Warriors \$2,500.06 8 8

<tbody>
</tbody>

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### jpvh8

##### New Member
=if(b2/100>c2,c2,quotient(b2,100))

#### faeryluv

##### New Member
Thank you so much!! That works great!!

#### jpvh8

##### New Member
Glad I could help. Good luck with the Walk.

#### shg

##### MrExcel MVP

Another way:

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

Thanks so much!!

#### faeryluv

##### New Member

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
 A B C D 1 Team Name Team Raised Individual Individual Raised 2 Happy Walkers \$FORMULA John Doe \$100

<tbody>
</tbody>

Sheet 2

 A B 1 Team Name Team Raised 2 Happy Walkers \$1,200

<tbody>
</tbody>

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.

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">M
 Team Name Team Raised Individual Individual Raised Happy Walkers \$ John Doe \$100

<tbody>
</tbody>

Sheet 2</body>

#### faeryluv

##### New Member
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
 A B C D 1 Team Name Team Raised Individual Individual Raised 2 Happy Walkers \$FORMULA John Doe \$100

<tbody>
</tbody>

Sheet 2

 A B 1 Team Name Team Raised 2 Happy Walkers \$1,200

<tbody>
</tbody>

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.

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

#### Michael M

##### Well-known Member
Try using this in B2....modify the range in red to suit

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

Last edited:

#### faeryluv

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

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

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

Replies
3
Views
428