formula help

[Viviank]

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

 

[Tom Ogilvy]

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.

 

[Kevin Lehrbass]

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]

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

 

[Tom Ogilvy]

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