formula to calculate variable differences

Topic

New Reply

We make boxes to sit on uneven ground so we use a laser level to determine the 4 different leg lengths. I’ve always then calculated the difference manually and simply added each difference to our post length to give us a level top. But I’d like to find a way to automate it, just to see if it can be done.

So, imagine 8 cells A1 – D1 then A2 to D2

B1 is height of Back Left Hole, lets say 1010mm

C1 is height of Back Right Hole, lets say 1000mm

B2 is height of Front Left Hole, lets say 1020mm

C2 is height of Front Right Hole, lets say 1030mm

So, Back Left post needs 10mm added to it’s length, Front Left needs 20mm and Front Right needs 30mm

Now, If I make

A1 formula =B1-C1, it will show 10 (as C1 is the lowest number…highest hole)

and D1 formula = C1-C1, it will show 0

and A2 formula =B2-C1, it will show 20

similarly D2 =C2-C1 to show 30

with C1 being the constant in all of these as it’s the lowest number.

(I wish I knew a way to post this as a small spreadsheet)

All good so long as the Back Right hole (C1) is always the lowest number…highest hole but this only happens 25% of the time on average.

So, is there a way I can tell each of the 4 destination cells (A1, D1, A2, D2) to work out which of the 4 origin cells (B1, C1, B2, C2) is the smallest number and use it as the constant?

Any thoughts?

Thanks

Chris

 

 

Reply | Quote

Replies

Seems like you could just use the built in min function in your formulas.

So in your example:    A1 = B1 – min(B1,C1,B2,C3)  etc.

Reply | Quote

A slight simplification on @N Banks’ answer: instead of min(B1,C1,B2,C2), as these are in a continguous block you could use min(B1:C2).

Reply | Quote

Thanks to both of you. Knew there would be a simple answer.

Reply | Quote