Need VBA Code or Formula to Find Unit Price Based on the number of Pages and Quantity Printed

[rsrasc]

Hi all,
I'm looking for a formula to find the unit price (column e) based on the number of pages (column d--number of pages could vary) and quantity printed (column c).

For example, if I want to print 40,100 papers with a number pages in this case to be 8 pages, the unit price would be $0.90. Any ideas on how to accomplish this.

Thank you in advance for any help!!!

Date	Day of Week	Quantity Printed	No. of Pages	Unit Price
1-Oct-13	Tuesday	40100	8	????
2-Oct-13	Wednesday	29800	12	????
3-Oct-13	Thursday	35000	16	????
4-Oct-13	Friday	2001	20	????
	Quantity Printed
	2,000-4,999	5,000-9,999	10,000-14,999	15,000-19,999	20,000-24,999	25,000-29,999	30,000-39,000	40,000-50,000
No. of Pages	1	2	3	4	5	6	7	8
8	$ 0.300	$ 0.235	$ 0.170	$ 0.140	$ 0.120	$ 0.110	$ 0.100	$ 0.900
12	$ 0.305	$ 0.240	$ 0.175	$ 0.145	$ 0.125	$ 0.115	$ 0.105	$ 0.950
16	$ 0.310	$ 0.245	$ 0.180	$ 0.150	$ 0.130	$ 0.120	$ 0.110	$ 0.100
20	$ 0.315	$ 0.250	$ 0.185	$ 0.155	$ 0.135	$ 0.125	$ 0.115	$ 0.105

<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>

 

[ChrisR]

Hi,

This is by no means efficient, but it's all I could think of based on how your data layout.

1) Change C5,D5,E5,F5,G5,H5,I5,J5 to your range that contains your numbers (I.e c5 will be the cell that has 2000-4999, D5 will be the cell that has 5000-7500 etc)

2) Change B1 to the cell that contains the quantity you are checking (I.e. 40100)

3) Change C1 to the cell number of number of pages required

4) Change C7:J7 to your range that contains the numbers 1-8 (In the grid below the quanity ranges)

5) Change B8:B11 to the range of the number of pages required (To the left of your table)

6) Change c8:J11 to the range of your table data (Excluding the numbers 1-8 above the table and the number of pages to the left)

7) Ctrl + Shift + Enter the formula so that { } brackets appear

8) Remove the " " marks from the greater than and lower than arrows (If I take them out the forum treats them as HTML I believe and removes half the code)

9) Very large and clunky but I've tested it and it works .......... Would be better to think of a more efficient layout though!

'=SUM(IF(VALUE(IF(AND(B1"">=""VALUE(LEFT(C5,-1+FIND("-",C5))),B1"<"VALUE(RIGHT(C5,-1+FIND("-",C5)))),1,IF(AND(B1"">=""VALUE(LEFT(D5,-1+FIND("-",D5))),B1"<"VALUE(RIGHT(D5,-1+FIND("-",D5)))),2,IF(AND(B1"">=""VALUE(LEFT(E5,-1+FIND("-",E5))),B1"<"VALUE(RIGHT(E5,-1+FIND("-",E5)))),3,IF(AND(B1"">=""VALUE(LEFT(F5,-1+FIND("-",F5))),B1"<"VALUE(RIGHT(F5,-1+FIND("-",F5)))),4,IF(AND(B1"">=""VALUE(LEFT(G5,-1+FIND("-",G5))),B1"<"VALUE(RIGHT(G5,-1+FIND("-",G5)))),5,IF(AND(B1"">=""VALUE(LEFT(H5,-1+FIND("-",H5))),B1"<"VALUE(RIGHT(H5,-1+FIND("-",H5)))),6,IF(AND(B1"">=""VALUE(LEFT(I5,-1+FIND("-",I5))),B1"<"VALUE(RIGHT(I5,-1+FIND("-",I5)))),7,IF(AND(B1"">=""VALUE(LEFT(J5,-1+FIND("-",J5))),B1"<"VALUE(RIGHT(J5,-1+FIND("-",J5)))),8,"ERROR")))))))))=$C$7:$J$7,IF($C$1=$B$8:$B$11,$C$8:$J$11,"ERROR")))

 

[ChrisR]

Removed......Edited original post

 

[etaf]

you can use
=IFERROR(INDEX($B$5:$I$8,MATCH(C12,$A$5:$A$8,0),MATCH(B12,$B$4:$I$4,1)),"not in table")

assuming the table is in A4 to I8

You just need to change the table titles as follows
instead of 2,000-4,999 you put 2000 -
instead of 5,000-9,999 you put 5000 etc
so just the first element

then for above 50000 - what do you want to do
my code will return the 40000-50000 for any number above 40000

 

[rsrasc]

Thank you both for assisting me on this. It's working great!!! No complaints!!!

Well done!!!

 

[etaf]

would this be an issue , as posted

then for above 50000 - what do you want to do
my code will return the 40000-50000 for any number above 40000

 

[rsrasc]

Hello Etaf,

Thank you for your help, and the answer to your question is that is not going to be an issue. The requirement so far does not exceed the 50000, therefore, at this point, your code or formula is providing me the correct unit prices. I hope this answer your question.

Sincerely,
Roberto