Limit values following a list

Hello,
I have a list of towns column A. A list of prices (5 per town) in column B. For the five prices of each town I have a maximum value and a minumum value. The list of max values is in column c and the list of min values is in column d.
I would like the prices to remain the same when they fall between the min and max value (in column e), or change to the max when the price is highet and to the min when the price is lower.

Please see file attached if not clear.

Thank you very much for helping!!!!!

Column E is empty. What exactly do you expect to happen?

BTW, the issue could have easily been illustrated with 50 rows of data and a file size of 15 KB instead of 1400 rows with a file size of 150 KB.

It would've been easier to copy the formulas if you had those mins and maxs in every row. Anyway I believe what you are looking for is like this:

=IF(B6<C6;B6;0)*IF(B6>D6;1;0)+IF(B6>C6;C6;0)+IF(B6<D6;D6;0)

Put that into E6 and copy with min and max locks upwards. Though as I previously said I would try to indicate mins and maxs in every row.

Of course you could build a macro for that - especially since you put this into programming section - but I really don't see the point of that.

I think this will do for Aarhus:

=MIN(MAX($B2;$D$6);$C$6)

for Aberdeen

=MIN(MAX($B7;$D$11);$C$11)

etc...

So the only problem is how to lock the mins and maxs into 5 row categories? I'd probably take couple of accessory cols along with the main formula. Like:

=IF(C2=0;(IF(C2<>0;0;C2)+IF(C2<>0;0;C3)+IF(C2<>0;0;C4)+IF(C2<>0;0;C5)+IF(C2<>0;0;C6));0)+IF(C2<>0;C2;0)

Put that into F2 and copy into G2 and copy them all the way down. Then change the formula in E column to get reference from F and G columns and take the row locks off.

Edit: I was just wondering why that ^ looks sooo too hard. Then it hit me: you can put "=SUM(C2:C6)" into F2 and copy etc and it works as fine... Also there was one if too much in the original line.

Hit the edit button in your thread open and go advanced. Change the title prefix into solved.