IF function with division and FLOOR function

faeryluv

New Member
Joined
Sep 1, 2018
Messages
30
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.


Team NameAmount Raised# Team MembersShirts Earned
Walk It Out$190.1421
Walkie Talkies$385.2553
Walking Warriors$2,500.0688

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Another way:

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

faeryluv

New Member
Joined
Sep 1, 2018
Messages
30

ADVERTISEMENT

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
ABCD
1Team NameTeam RaisedIndividualIndividual Raised
2Happy Walkers$FORMULAJohn Doe$100

<tbody>
</tbody>

Sheet 2

AB
1Team NameTeam Raised
2Happy 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.

Thanks for your time! :biggrin:

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

<tbody>
</tbody>

Sheet 2</body>
 

faeryluv

New Member
Joined
Sep 1, 2018
Messages
30
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
ABCD
1Team NameTeam RaisedIndividualIndividual Raised
2Happy Walkers$FORMULAJohn Doe$100

<tbody>
</tbody>

Sheet 2

AB
1Team NameTeam Raised
2Happy 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.

Thanks for your time! :biggrin:

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,269
Office Version
  1. 2013
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,272
Messages
5,527,710
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top