Formula for value entered between YxY amount

[rossw8]

Good morning.

I'm currently working on a pricing matrix and I am completely stumped trying to figure out the formula I need. I was hoping someone could help me out?

What I am hoping to acheive:

Unit size entered if between 588x1038 to 588x1188 will change amount in next cell to 155.73.
Unit size entered if between 588x118 to 588x1488 will change amount in next cell to 163.85.

Im hoping to do this with quite a few different sizes/prices but just need an example of the formula to use.

I've attached a small excel file as an "example".

Thanks in advance. Formula Example.xlsx

[qiyusi]

why don't you split the unit size.

please check attached example. Formula can be modify depend on your size variation.

Unit size entered if between 588x118 to 588x1488 will change amount in next cell to 163.85. << i assume the size is 500x 1189 or above , not 118?

[Glenn Kennedy]

You could try this:
=IF(MID(D1,FIND("x",D1)+1,255)+0>1188,163.85,155.73)
However, I suspect that this is not really what you want, as your example in the sheet contained neither 163.85 nor 155.73 in G1. You may need to explain with more detail.

One other thing, you have used a number of merged cells. Sooner or later, this will cause you massive problems in your formulae. You are best avoiding them altogether in data columns. It's best to leave data in single cells, widening them as needed.

[Beamernsw]

If you made a helper column (say column J) using this formula:-

Please Login or Register  to view this content.

it could give you a list of numbers like 5881038 (width and length combined).
Then if you place the prices beside them you could use a simple vlookup to get your results.

Here's an example.

[rossw8]

Thank you for the responses, unfortunately, i cant seem to be able to open any of the attachments

[Glenn Kennedy]

There was no attachment in my response. Did you try it? Did it work? Etc, etc....

[rossw8]

I have it working now. I have used Beamernsw idea and it seems to be working just how I'm needing it too. Just need to tweak it slighty. Thanks everyone. Great help as always