+ Reply to Thread
Results 1 to 5 of 5

IF Function

  1. #1
    Registered User
    Join Date
    06-15-2007
    Posts
    3

    Exclamation IF Function

    Ok - I can figure out basic functions, but this one is confusing. Here's my problem - there is 2 IF formula's needed

    It's a spreadsheet to track a contest.
    1st formula needed: For your first $12,500 AP during the contest
    period, you earn 1 ticket. For every $3,000 AP
    above the initial $12,500 during the contest
    period, you earn another ticket.

    I need to have it calculate how many tickets a person gets from the sum of their AP. I have the total calculating already.

    2nd one is: For each week in the contest period that your
    district achieves:
    150% of MPI, you will receive 1 ticket
    200% MPI – you earn 3 total tickets for that
    week!

    This one is even more confusing... MPI is our name for our quotas.

    I am not sure how to do the formula. Would someone please show me what it would be? Then I can figure it out for the future ones. Thank you.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    For the first question. If the sum is in A1 use this formula for the number of tickets

    =MAX(0,INT((A1-500)/3000)-3)

    For second question, assuming you have each week's % in a separate cell, say the range is D1:D10

    =COUNTIF(D1:D10,">=1.5")*1+COUNTIF(D1:D10,">=2")*2

  3. #3
    Registered User
    Join Date
    06-15-2007
    Posts
    3

    Not Working

    I don't want it to round up - so is there a change in the calculations? I figured out why it was messing up, I did forget to change one reference. But I need it to round down, not up.

    help pls... .thanks!!! I appreciate that you are responding... this is really beyond me. Thanks!


    Quote Originally Posted by daddylonglegs
    For the first question. If the sum is in A1 use this formula for the number of tickets

    =MAX(0,INT((A1-500)/3000)-3)

    For second question, assuming you have each week's % in a separate cell, say the range is D1:D10

    =COUNTIF(D1:D10,">=1.5")*1+COUNTIF(D1:D10,">=2")*2
    Last edited by VAB; 06-18-2007 at 10:55 AM.

  4. #4
    Registered User
    Join Date
    06-15-2007
    Posts
    3

    2nd Function Not Working

    The 2nd one is showing 3 tickets for everyone regardless of what their MPI is...

    Each week is in a separate column.
    Name - col A
    Wk 24 in col b
    Total Tickets in column C
    Wk 25 in Col D
    Total Tickets in column E
    Wk 26 in Col F
    Total Tickets in column G
    Total of all tickets in column H

    I entered 251% for wk 24's MPI and it said 3 tickets... that's correct as it's over 200%
    but the next person's was 155% and it should be 1 ticket and it's showing 3 tickets. here's the formula:
    =COUNTIF(B6,">=150%")*1+COUNTIF(B6,">=200%")*2
    what is wrong? (and I don't want it to round up.)

    Thanks

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    To force a rounding down of the result, just surround the formula with a Rounddown() function and set the number of decimal places you want.

    e.g. =Rounddown(your_formula,2) where your_formula is either of the formulas given by daddylonglegs and 2 is the number of decimal places.

    For your second problem make sure that you have automatic calculations on. Go to Tools|Options and select Automatic from the Calculation tab.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1