IF function with division and FLOOR function

faeryluv

faeryluv

New Member
Joined
Sep 1, 2018
Messages
47
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]
 
Sort by date Sort by votes

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I've got one more...

A participant earns a shirt if:
they have raised $100 or more or if
their team collectively raised enough for all team members to have a shirt.

Something to the effect of:

In Column C:
enter "yes" if E2 >= 100, or if G2/100 is >= I2 but > 0 otherwise enter "no"

[TABLE="class: grid, width: 811"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Last
Name
[/TD]
[TD]First
Name
[/TD]
[TD]Earned
Shirt?
[/TD]
[TD]Shirt
Size
[/TD]
[TD]Individual
Raised
[/TD]
[TD]Team Name
[/TD]
[TD]Team
Raised
[/TD]
[TD] # of
team mbrs
[/TD]
[TD]# of shirts
team earned
[/TD]
[TD]all team mbrs
earned shirts?
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Johnson
[/TD]
[TD]John
[/TD]
[TD]No
[/TD]
[TD]Large
[/TD]
[TD]$0.00
[/TD]
[TD] (registered without a team)
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Hughes
[/TD]
[TD]Hugh
[/TD]
[TD]Yes
[/TD]
[TD]Medium
[/TD]
[TD]$100.00
[/TD]
[TD] (registered without a team)
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Adamson
[/TD]
[TD]Adam
[/TD]
[TD]No
[/TD]
[TD]Large
[/TD]
[TD]$0.00
[/TD]
[TD]Wacky Walkers[/TD]
[TD]$400.00
[/TD]
[TD]13
[/TD]
[TD]4
[/TD]
[TD]FALSE
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Stevenson
[/TD]
[TD]Steve
[/TD]
[TD]Yes
[/TD]
[TD]Medium
[/TD]
[TD]$40.00
[/TD]
[TD]Walkie Talkies[/TD]
[TD]$496.09
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

I had the formula, but it wasn't working for those that weren't registered on a team. Those people had "0" team members and "0" shirts earned so the formula picked it up as yes since both columns were equal.

I really hope I'm making sense, lol!

Any help is much appreciated!:):grin::-)
 
Upvote 0
Maybe this....

Code: 
=IF(H2="","No",IF(OR(E2>100,G2/100>=I2),"Yes","No"))
 
Upvote 0
Ok, so I think I am not getting it clearly...try this......maybe I need a coffee !!!

Code: 
=IF(AND(G2="",E2 > 100),"Yes",IF(OR(I2=0,G2/100 < I2),"No","Yes"))
 
Last edited:
Upvote 0
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/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]Earned
Shirt?
[/TD]
[TD]Individual
Raised
[/TD]
[TD]Team
Raised
[/TD]
[TD] # of
team mbrs
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]formula[/TD]
[TD]$0.00
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]formula[/TD]
[TD]$100.00
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]formula[/TD]
[TD]$0.00
[/TD]
[TD]$400.00
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]formula[/TD]
[TD]$40.00
[/TD]
[TD]$496.09
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

I know it is so confusing, lol! I am sorry :(

So column A should say yes or no based on whether Individual Raised is equal to or more than $100 OR if the team raised enough for all members to have a shirt. You will see row 4 the individual didn't raise any money and the team only raised $400 which does not cover all 13 team members so that should say NO in A4. Row 5 however, there are 3 team members and they raised almost $500 so even though the individual only raised $40, they still get a shirt because the team raised enough for all of them.

The problem comes with row 2 and 3. Those individuals are not on a team. That means they can't rely on the team to raise enough for them. They have to raise at least $100 on their own in order to get a shirt.

I hope that makes more sense? I have close to 3,000 people that are constantly raising money so when I have to run a report at the last minute before our Walk, I have to apply the formula really quick and print the report for volunteers to hand out shirts.
 
Upvote 0
oooooooow, my head hurts !!!

Code: 
=IF(AND(G3="",E3>100),"Yes",IF(OR(G3=0,H3=0),"No",IF(G3/H3<100,"No","Yes")))
 
Upvote 0
Michael M said:
oooooooow, my head hurts !!!

Code: 
=IF(AND(G3="",E3>100),"Yes",IF(OR(G3=0,H3=0),"No",IF(G3/H3<100,"No","Yes")))
Click to expand...


I'm sorry :( I don't mean to make your head hurt.
So here's the code I am using, but it returns #N/A on the ones that don't have teams :(
Code: 
=IF(OR(E2>=100,AND(G2/100>=H2,H2>0)),"yes","no")

[TABLE="class: grid, width: 399"]
<tbody>[TR]
[TD]Shirts Earned[/TD]
[TD]Individual Raised[/TD]
[TD]TEAM
RAISED[/TD]
[TD] # OF
TEAM
MBRS[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$4,884.52[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0.00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$285.00[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0.00[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0.00[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]$616.09[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$1,940.00[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$765.00[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$765.00[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD="align: right"]317.63[/TD]
[TD="align: right"]$2,746.90[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$100.00[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$465.00[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]$250.00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$250.00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]yes[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]$250.00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$250.00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$250.00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$250.00[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$250.00[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Actually, I think I got it to work. The last two columns were formulas so I just copied and pasted them back as values so I think it's fine now.

Thank you for all your help! :)
 
Upvote 0
Are you actually working with columns A:J as shown in post 12, or only 4 columns (what are they) as shown in post 18?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
SUMPRODUCT & RIGHT Functions
Replies
3
Views
1K
Oaktree
Oaktree

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top