How calculate price based on table?
This one will probably be a little wild but here goes...
I have a table which is a "grid" to use for pricing of an item or items. The grid looks something like...
Item 1 2 3 4 5 6 7 8
1 $66.00 $46.00 $39.33 $37.00 $35.00 $30.70 $30.70 $30.70
2 $26.00 $16.50 $16.00 $15.75 $15.20 $14.50 $14.50 $14.50
3 $31.00 $20.50 $20.00 $18.75 $18.20 $18.00 $18.00 $18.00
4 $25.00 $16.50 $16.33 $15.50 $15.00 $14.50 $14.50 $14.50
Then, I have an unbound form which contains a field for the user to choose the item (Item in the grid above), and another field where they enter the quantity they wish to order. If, for example, the user chooses item #2 and enters 4 in the quantity field, then I have a third field where the cost needs to show which in this case would be $15.75.
How do I get that amount to show up in the cost field?
But then, for example, if they order item #2, but want 7 of them, the cost needs to be displayed at $15.20 PLUS $18.00 for the 6th and 7th each or $51.20 total because each unit after the 5th one should be priced at $18.00 each.
Confusing I know but any help will be appreciated.
--Steve
I have a table which is a "grid" to use for pricing of an item or items. The grid looks something like...
Item 1 2 3 4 5 6 7 8
1 $66.00 $46.00 $39.33 $37.00 $35.00 $30.70 $30.70 $30.70
2 $26.00 $16.50 $16.00 $15.75 $15.20 $14.50 $14.50 $14.50
3 $31.00 $20.50 $20.00 $18.75 $18.20 $18.00 $18.00 $18.00
4 $25.00 $16.50 $16.33 $15.50 $15.00 $14.50 $14.50 $14.50
Then, I have an unbound form which contains a field for the user to choose the item (Item in the grid above), and another field where they enter the quantity they wish to order. If, for example, the user chooses item #2 and enters 4 in the quantity field, then I have a third field where the cost needs to show which in this case would be $15.75.
How do I get that amount to show up in the cost field?
But then, for example, if they order item #2, but want 7 of them, the cost needs to be displayed at $15.20 PLUS $18.00 for the 6th and 7th each or $51.20 total because each unit after the 5th one should be priced at $18.00 each.
Confusing I know but any help will be appreciated.
--Steve
MacNuttin
Just trying to understand, for example, if they order item #3, but want 7 of them, the cost needs to be displayed at $18.20 PLUS $18.00 for the 6th and 7th each or $127 total because each unit after the 5th one should be priced at $18.00 each? I think that the table gives the cost per item decreasing as more are ordered up to the 5th item and then there is no more decrease in the cost per item.
ASKER
Yes, $127.00 is the correct answer in your scenario. Actually, the $18.00 each figure holds true for quantities of 6-10. At 11 - 15 it drops but I didn't show it in my grid example due to lack of space.
--Steve
--Steve
Actually I think that 7*$18 = $126.00 is the correct answer. Are you working in Excel2003?
ASKER
Its 5 x $18.20 = $91.00 plus $18.00 x 2 = $36.00 or $127.00 total. The first 5 get calculated at the $18.20 price.
--Steve
--Steve
I did it in excel2003 here's the spreadsheet:
No.Items 1 2 3 4 5 6 7 8
Item1 $66.00 $46.00 $39.33 $37.00 $35.00 $30.70 $30.70 $30.70
Item2 $26.00 $16.50 $16.00 $15.75 $15.20 $14.50 $14.50 $14.50
Item3 $31.00 $20.50 $20.00 $18.75 $18.20 $18.00 $18.00 $18.00
Item4 $25.00 $16.50 $16.33 $15.50 $15.00 $14.50 $14.50 $14.50
Enter Item #: 2
Item # entered: Item2
Enter # in order 5
column # 6
row # 3
address: $F$3
Price per item: $15.20
Total: $76.00
No.Items 1 2 3 4 5 6 7 8
Item1 $66.00 $46.00 $39.33 $37.00 $35.00 $30.70 $30.70 $30.70
Item2 $26.00 $16.50 $16.00 $15.75 $15.20 $14.50 $14.50 $14.50
Item3 $31.00 $20.50 $20.00 $18.75 $18.20 $18.00 $18.00 $18.00
Item4 $25.00 $16.50 $16.33 $15.50 $15.00 $14.50 $14.50 $14.50
Enter Item #: 2
Item # entered: Item2
Enter # in order 5
column # 6
row # 3
address: $F$3
Price per item: $15.20
Total: $76.00
Here's the formula:
Item # entered: ="Item"&B7
column # =B10+1
row# =B7+1
address: =ADDRESS(B12,B11)
Price per item: =INDIRECT(B13)
Total: =B14*B10
Item # entered: ="Item"&B7
column # =B10+1
row# =B7+1
address: =ADDRESS(B12,B11)
Price per item: =INDIRECT(B13)
Total: =B14*B10
ASKER
But how do I do this in Access?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Wow! I'm sorry. I thought I was in the Access "area". My mistake. Thanks.
--Steve
--Steve
The way I have it setup is this:
Column A and Row 1 have your table headers
B2:I5 represent your price values
I have two cells where you input the item number (C8) and the quantity (C9).
In cell C11, I have this formula. I added the +1 because of the way vlookup works.
=VLOOKUP(C8,$A$2:$I$5,C9+1
Column A and Row 1 have your table headers
B2:I5 represent your price values
I have two cells where you input the item number (C8) and the quantity (C9).
In cell C11, I have this formula. I added the +1 because of the way vlookup works.
=VLOOKUP(C8,$A$2:$I$5,C9+1
I think your pricing table should be designed differently, or converted to this format for lookup from the form:
Product# Qty Price
1 1 $66.00
1 2 $46.00 etc
Then it will be easier to write a dlookup to find the right price.
Do you have any flexibility on the design?
Regards
Mike
Product# Qty Price
1 1 $66.00
1 2 $46.00 etc
Then it will be easier to write a dlookup to find the right price.
Do you have any flexibility on the design?
Regards
Mike
Hi SteveL13 :-),
Since we haven't heard from you for a couple of days could you please give us an update on the status of this question?
See: https://www.experts-exchange.com/help.jsp#hi51 Thank you, turn123's friendly update request script.
Offtopic comments about this script to https://www.experts-exchange.com/questions/21188389/Unanswered-question-list-and-ping-feedback-and-bugs.html please :-).
Since we haven't heard from you for a couple of days could you please give us an update on the status of this question?
See: https://www.experts-exchange.com/help.jsp#hi51 Thank you, turn123's friendly update request script.
Offtopic comments about this script to https://www.experts-exchange.com/questions/21188389/Unanswered-question-list-and-ping-feedback-and-bugs.html please :-).