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

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" wrote in message
...
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

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

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

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

--
Regards,
Tom Ogilvy

"Kevin Lehrbass" wrote in message
...
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