+ Reply to Thread
Results 1 to 5 of 5

formula help

  1. #1
    Viviank
    Guest

    formula help

    I volunteered to help set up a spreadsheet for a school fundraiser. I need
    help with a formula. The teacher wants to keep track of student profits.
    Basically, if a student sells 1-10 items, they receive $20 of credit, if they
    sell 11-20 items, they receive $20 in the account, etc, How would I set up a
    formula like that?? HELP

    Vivian K

  2. #2
    Tom Ogilvy
    Guest

    Re: formula help

    set up a table with the quantity to sell in one column and the corresponding
    credit in the next column.

    Then you can use a Vlookup formula using the actual amount sold as the
    lookup value to return the credit.

    See the help on Vlookup to see what you need to do.

    --
    Regards,
    Tom Ogilvy

    "Viviank" <[email protected]> wrote in message
    news:[email protected]...
    > I volunteered to help set up a spreadsheet for a school fundraiser. I

    need
    > help with a formula. The teacher wants to keep track of student profits.
    > Basically, if a student sells 1-10 items, they receive $20 of credit, if

    they
    > sell 11-20 items, they receive $20 in the account, etc, How would I set up

    a
    > formula like that?? HELP
    >
    > Vivian K




  3. #3
    Kevin Lehrbass
    Guest

    RE: formula help

    Hi Viviank,

    Try using a vlookup formula like this:
    =VLOOKUP(A1,{1,20;11,30;20,30},2,TRUE)
    After pasting this formula into the formula bar, you MUST press
    CTRL+SHIFT+ENTER as this is an array formula.
    In the formula, if the number of units sold is >= 1 and less than 11 then
    the formula returns 20. If the number of units sold is >= 11 and less than
    20 then the formula returns 30, etc.
    You can modify the numbers as required.
    Send me an email if you have any questions.
    Kevin Lehrbass
    www.spreadsheetsolutions4u.com


    "Viviank" wrote:

    > I volunteered to help set up a spreadsheet for a school fundraiser. I need
    > help with a formula. The teacher wants to keep track of student profits.
    > Basically, if a student sells 1-10 items, they receive $20 of credit, if they
    > sell 11-20 items, they receive $20 in the account, etc, How would I set up a
    > formula like that?? HELP
    >
    > Vivian K


  4. #4
    Viviank
    Guest

    RE: formula help

    You rock! I would never have figured that out. Now I will look like a
    genius to the choir teacher. Thanks a lot.

    "Kevin Lehrbass" wrote:

    > Hi Viviank,
    >
    > Try using a vlookup formula like this:
    > =VLOOKUP(A1,{1,20;11,30;20,30},2,TRUE)
    > After pasting this formula into the formula bar, you MUST press
    > CTRL+SHIFT+ENTER as this is an array formula.
    > In the formula, if the number of units sold is >= 1 and less than 11 then
    > the formula returns 20. If the number of units sold is >= 11 and less than
    > 20 then the formula returns 30, etc.
    > You can modify the numbers as required.
    > Send me an email if you have any questions.
    > Kevin Lehrbass
    > www.spreadsheetsolutions4u.com
    >
    >
    > "Viviank" wrote:
    >
    > > I volunteered to help set up a spreadsheet for a school fundraiser. I need
    > > help with a formula. The teacher wants to keep track of student profits.
    > > Basically, if a student sells 1-10 items, they receive $20 of credit, if they
    > > sell 11-20 items, they receive $20 in the account, etc, How would I set up a
    > > formula like that?? HELP
    > >
    > > Vivian K


  5. #5
    Tom Ogilvy
    Guest

    Re: formula help

    that isn't an array formula and doesn't require entry with Ctrl+Shift+Enter

    --
    Regards,
    Tom Ogilvy

    "Kevin Lehrbass" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Viviank,
    >
    > Try using a vlookup formula like this:
    > =VLOOKUP(A1,{1,20;11,30;20,30},2,TRUE)
    > After pasting this formula into the formula bar, you MUST press
    > CTRL+SHIFT+ENTER as this is an array formula.
    > In the formula, if the number of units sold is >= 1 and less than 11 then
    > the formula returns 20. If the number of units sold is >= 11 and less

    than
    > 20 then the formula returns 30, etc.
    > You can modify the numbers as required.
    > Send me an email if you have any questions.
    > Kevin Lehrbass
    > www.spreadsheetsolutions4u.com
    >
    >
    > "Viviank" wrote:
    >
    > > I volunteered to help set up a spreadsheet for a school fundraiser. I

    need
    > > help with a formula. The teacher wants to keep track of student

    profits.
    > > Basically, if a student sells 1-10 items, they receive $20 of credit, if

    they
    > > sell 11-20 items, they receive $20 in the account, etc, How would I set

    up a
    > > formula like that?? HELP
    > >
    > > Vivian K




+ 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