Combining a formula creates different result

[Median]

*combining a formula creates different result*

I just wondered why the following is happening

The formula in KU2, looks at the value in KT2, for it's calculation. Therefore, to save an unneeded column, I thought replacing "KT2" in the KU2 formula (with the formula in KT2), would give exactly the same result. But for some reason it looks like it's it erroring - & I'm not quite sure why... & the formula isn't very "nice"...!

Any help would be massively appreciated

KU2 - KU7 contains the original formula (correct result)

KU8 - KU13 contains the merged formula (strange error)

Thanks

[JohnTopley]

I don't know what the formulae are attempting to do but I suspect not many of use are unwilling to unscramble it!

Explain exactly what you are trying to calculate as I find it difficult to believe it cannot be simplified.

[Roel Jongman]

holy something that's one big bad mother of all formula's
This is the first time in my life I had a brain-freeze without eating ice-cream..


/me: someone have the phonenumber of Albert Einstein.. he was rather good with complex formulas I heard

[JohnTopley]

Pure coincidence I am sure but

in KU2

=IFERROR(INDEX($A$1:$KN$1,MATCH(KT2,$A2:$KN2,0)),"")

copied down

gives the same results as your current formula!

[Median]

holy something that's one big bad mother of all formula's
This is the first time in my life I had a brain-freeze without eating ice-cream..


/me: someone have the phonenumber of Albert Einstein.. he was rather good with complex formulas I heard

It's funny, what it's actually calculating is quite straightforward - but the amount of stages & caveats, have made so so long!! It's the end result of merging god knows how many formulas already!!

[Median]

Pure coincidence I am sure but

in KU2

=IFERROR(INDEX($A$1:$KN$1,MATCH(KT2,$A2:$KN2,0)),"")

copied down

gives the same results as your current formula!

Nearly John!!

However, it has to be connected to KS1 & KV1 (variable array size) (your formula works for 300 - 1)

Also, I believe your formula looks for a perfect match - there's an added complication - where if the value is not present, it needs to do a 2nd check to see if the value was "passed" (i.e. if it was passed, tell me the location of this value, instead of the perfect match) i.e..

lnH2YoSkTMa4QBIB0r9w7g.png

[JohnTopley]

So what is the basic calculation as the very straightforward one in post #4 worked (as I said more by chance ???).

What determines the "lookup" value returned from row 1?

EDIT: so what is the connection with KS1 and KV1 as they appear to be the limits of values in row 1?

[Median]

That's right they are the limits.

The formula is looking for the 1st touch/cross below the value stated in KT - then telling me the position in the total array. A quick example (the "long formula" is below the solid line)

-G9j-OgdTwiTB87LelhtxA.png

[Jonmo1]

I'd just like to refer to one of my favorite rules of thumb:
If it ain't broke, don't fix it.

If you have a working set of formulas, that perform your expected functions, then leave it be.
Excel gives you over 16,000 columns to work with, it's ok to use a few of them.
In fact, usually the calculations are more efficient when you split calculations among multiple columns instead of cramming all calculations into a single larger formula.

If the formula in KU2 refers to KT2 2 or more times, and you combine KT2 into KU2, then that calculation is repeated 2 or more times in KU2. Where it's only done once in KT2 originally.

[Median]

I'd just like to refer to one of my favorite rules of thumb:
If it ain't broke, don't fix it.

The only problem, that formula gets duplicated - a lot in my proper sheet, & the amount of time it takes to calculate for all fields (i.e. thousands of rows) gradually gets slower & slower!! I'm convinced there must be ways to simplify it (& make error checking easier i.e. OP)

The real sheet looks like this... thank goodness there's a size limit on here

yIJXftEiTXa5YB2YQVsLEg.png

[Jonmo1]

Well, simply taking the formula from KT and combining it into KU (looks like 8 references in KU to KT) is not going to make it better.
If anything, it would make it worse.

it looks like JohnTopley is onto something, I'd go in that direction.
I'd agree with John that the relationship of KS1 and KV1 is not clear.

[JohnTopley]

For KU3 you get 153 I get 151: why the difference? All the others agree (KU2:KU7)

Logging off now!

[Median]

It's might be worth having a read of this thread - although the values are different the concept is identical - & the long formula is a result of everything in this thread (https://www.excelforum.com/excel-for...formula-2.html)

However the key post - is this one by Flame (post #18). Hopefully that clears up what KS1 & KV1's job is (i.e. they define the calculation range)

The reason you're getting 152 is explained, hopefully, in post #24. Those tables are basically the inner workings of the long formula (lots of IF's & checks)

If you need further clarification - ask away

[JohnTopley]

See post #9 !!!!

[JohnTopley]

Curiosity getting the better of me:

Do your ranges always go from 1 to n? If so, how variable is n (KS1)? Examples have been 100 and 300 only.

And what is logic on MATCH vs KT2:

Equal : OK

Less than/greater than i.e no exact match ???

Out of range: blank

[Median]

Not necessarily; for example:

fwZM_vaJQceKMDr5j58SrA.png

It needs to find the first number less than KT2. If it finds this number (Match1)(within the dynamic range), it then tries to finds the first value equal to KT2 (Match0). If Match 0 occurs first, this is the value which gets displayed

(the most important aspect of the formula is detecting "bound breaks")

[JohnTopley]

Curiosity satisfied!!!