How Calculate range Min Number & Max Number (Optical)

[johnreid7477]

Dear All,

I want ask .. can Excel calculate number range -10.00 - +9.00 = pricelist, if -15.00 - +8.00 = Outpricelist (add cost).

My example file in attachment.


Thanks - John

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

[etaf]

If the price in cell SPH (column B) is that all you are testing

-10.00-+9.00 = pricelist, if -15.00-+8.00 = Outpricelist.

not sure I understand

But you could use an IF statement in cell F3

=IF( OR ( B2<=-10 , B2 =>9), 20, 10)

so if the number in cell B2 is less that -10 or greater than +9 - use 20 , otherwise use 10 in F3

is this what you are after
if not can you give further examples of the rules to apply

[johnreid7477]

Thanks for etaf, that's open my basic excel and now i can give new description detail.

Example :

Please Login or Register  to view this content.

My question : in SHEET1 can i put to SHEET2 for logical (F2 : F5),

from cost PRODUCT A = IF RANGE FROM SHEET1 (B2<=-10.00, C2>=10.00), AND (D3<=-10.00, E3>=10.00), AND (F4<=-10.00, G4>=10.00) = $10, out range ADD COST $1 ($10 + $1 = $11)

and cost PRODUCT B = IF RANGE FROM SHEET1 (B2<=-15.00, C2>=15.00), AND (D3<=-15.00, E3>=15.00), AND (F4<=-15.00, G4>=15.00) = $50, out range ADD COST $2 ($50 + $2 = $52)

My logical complicated maybe i'm make mistake, or can give me better solution.


Thanks - John

[etaf]

from cost PRODUCT A = IF RANGE FROM SHEET1 from cost PRODUCT A = IF RANGE FROM SHEET1 (B2<=-10.00, C2>=10.00), AND (D3<=-10.00, E3>=10.00), AND (F4<=-10.00, G4>=10.00) = $10, out range ADD COST $1 ($10 + $1 = $11) = $10, out range ADD COST $1 ($10 + $1 = $11)

and cost PRODUCT B = IF RANGE FROM SHEET1 (B2<=-15.00, C2>=15.00), AND (D3<=-15.00, E3>=15.00), AND (F4<=-15.00, G4>=15.00) = $50, out range ADD COST $2 ($50 + $2 = $52)

this should give you the price as descibed
=IF(INVOICE!A2="Product A",IF(AND(PRODUCT!B2<=-10,PRODUCT!C2>=10,PRODUCT!D3<=-10,PRODUCT!E3>=10,PRODUCT!F4<=-10,PRODUCT!G4>=10),10,11),IF(INVOICE!A2="Product B",IF(AND(PRODUCT!B2<=-15,PRODUCT!C2>=15,PRODUCT!D3<=-15,PRODUCT!E3>=15,PRODUCT!F4<=-15,PRODUCT!G4>=15),50,52),"Not Product A or B"))

you could also use vlookup to get the other values off the product sheet

on your invoice you have
SPH CYL

BUT on the Product sheet you have
SPH MIN SPH MAX CYL MIN CYL MAX

so not sure which one you want to pickup

This maynot works as I assume you will have 100's of products
so we need to use a vlookup or indexmatch to obtain the values

[johnreid7477]

this should give you the price as descibed
=IF(INVOICE!A2="Product A",IF(AND(PRODUCT!B2<=-10,PRODUCT!C2>=10,PRODUCT!D3<=-10,PRODUCT!E3>=10,PRODUCT!F4<=-10,PRODUCT!G4>=10),10,11),IF(INVOICE!A2="Product B",IF(AND(PRODUCT!B2<=-15,PRODUCT!C2>=15,PRODUCT!D3<=-15,PRODUCT!E3>=15,PRODUCT!F4<=-15,PRODUCT!G4>=15),50,52),"Not Product A or B"))

you could also use vlookup to get the other values off the product sheet

on your invoice you have
SPH CYL

BUT on the Product sheet you have
SPH MIN SPH MAX CYL MIN CYL MAX

so not sure which one you want to pickup

This maynot works as I assume you will have 100's of products
so we need to use a vlookup or indexmatch to obtain the values

Thanks again etaf, yes my original pricelist have 30 product and my example only 2 product need short formula or i change product list like this :

Please Login or Register  to view this content.

I think it can be easier with this separate table where value min to max, but sorry if my table make you confuse maybe i must remove SHEET1 (PRODUCT) :

Please Login or Register  to view this content.

Ok give me time for try google with example about logical optical lenses and learn more vlookup, i'm must avoid bad question.


Thanks - John