Unknown Formulae (When several values are Equal/Greater than)

[Geforce]

Hi to all,

I'll get straight down to business.

D7 = Value in mm (Container Length)
D8 = Value in mm (Container Width)

J22 = Cost of lining a container.

However, depending on the size of the container, the value that J22 is multiplied by chages, as is not pro-rata.

I'll explain:

If D7 < 6095, then J22 * 64.59
If D7 => 6096 < 9143, then J22 * 56.52
If D7 => 9144 < 12192, then J22 * 54.28
If D7 => 12192, then J22 * 50.80

Also,

If D8 > 2438, then J22 * 50.80.

How do I encorporate that into cell J22?

[NBVC]

Not sure which of D7 or D8 takes precedence, but assuming its D8...

In any cell other than D7,D8 or J22...

=J22*If(D8>2438,50.80,Lookup(D7,{0,6096,9144,12192},{64.59,56.52,54.28,50.80})

[Geforce]

Not sure which of D7 or D8 takes precedence, but assuming its D8...

In any cell other than D7,D8 or J22...

=J22*If(D8>2438,50.80,Lookup(D7,{0,6096,9144,12192},{64.59,56.52,54.28,50.80})

Hi HBVC and thanks for the immediate reply.

I've been playing with your cell input suggestion and it works just fine with a few small amendments, however, the values 64.59 56.52 54.28 and 50.80 are not static - They are variable and need linking in with another cell/form.

When I try to change the value in the Array to the Cell specific with the value, it just throws up errors. Is there a way around this do you know?

[NBVC]

You can set up a table on the side which list the bottom ranges and the corresponding factor... then reference that in the formula..

e.g. if you setup the table in X1:Y4

=J22*If(D8>2438,50.80,Lookup(D7,$X$1:$Y$4))

[Geforce]

Yeah, scrap my post, I figured I could just remove the brackets and type for example

L7:L10



Thanks again.