Search results

Thanks Monty for your share. I will follow up the platforms you mention.

I'm trying to find a solution for this: my company's current model is using a dangerously old Excel version (developed 1997!!). I'm finding it surprisingly hard to find one, especially ones for sale where the provider guarantees upgrades and bug fixes for a defined period. (There are plenty of...

@John Jairo V I believe I managed to replicate your LET function using Named Formulas. Thanks so much for your help. See attached. One more thing: I seem to have resolved this without recourse to either your variable f or the complex SWITCH / CHOOSE ... your thoughts? juanito

Thanks @Peter Bartholomew (and apologies for not responding earlier). Unfortunately, the order requirement doesn't suit my model and I was looking for a more general solution - which I have now found thanks to the support on this thread

Interesting, I will follow up

That is awesome, sir! My (corporate) version of 365 doesn't support LET yet - however I believe I may be able to replicate the functionality using named formulas... it may take me a day or two to confirm.

Impressive stuff p45cal - some non-interface M coding in there, it would appear? Many thanks...

Thanks vletm! And your solution does indeed evaluate correctly, independent of row order. Well done! Ideally I would have found a formula solution but your code does the job. Best, juanito

Hi vletm! By a general solution I mean for a data table with any number of entries and sorted in any order. The Values should be weighted by each "OU", depending on total Hours. I believe that the initial attachment that I included should be sufficiently indicative, but I am very happy to expand...

Thanks! In this case we are still without a "general" solution. It may be that there isn't a satisfactory answer and I must find an alternative method (PQ perhaps).

Hi again vletm and I sincerely appreciate you staying close to this. It is true that your example was similar in structure but the data was a little different, so I was wrong to expect the same result. I took your model and pasted in the exact same data. Your Solve evaluated to 71% when the...

Thanks, bosco_yip! The problem with this approach is that the rows must be ordered in pairs, correct? I am looking for a more general solution which works independently of order and references column C...

Thanks again vietm... only problem here is that the evaluation doesn't give the correct answer!!

Thanks John - fascinating use of MMULT, a function which I keep telling myself I need to understand better. But it doesn't give me the general solution I am seeking, which would function over a potentially larger dataset, with rows in any order etc...

Thanks vletm! Going to sleep now but more tomorrow I promise!

Thank you John for your reply! I’ll respond tomorrow in euro time.

Hi all - I want to find a formula which can extract a weighted average where there is only one Values column. I can do this by pivoting into two columns easily enough (see solution to the right), but prefer to find a formula which can act directly on the data table as presented... and I haven't...

Thank you Narayan - The issue was with the scope, and I have now fixed it. - juanito

I have a named formula for dynamic range "_dq" defined as follows: =OFFSET(qualdata!$A$2;;;COUNTA(qualdata!$A:$A)-1;COUNTA(qualdata!$1:$1)) I am getting #NAME? errors referencing this formula from other worksheets. If on the 'qualdata' source worksheet, I can type F5 go to, "_dq" and the...

It's probably wiser to search for the entire " to " string in the source string - otherwise if your first city is New York for example then the formula above won't work because it is searching for a space and the city name contains a space. Try =LEFT(A1;SEARCH(" to ";A1)-1) for the first city...

Dave, I can't see a file

You may need to select the individual column again after demerging? (After demerging two columns are selected?)

Yes, that's probably your best option then - juanito

You'd have to use VBA I believe (I'm no VBA expert). But I continue to support bobhc's advise to avoid merging in (nearly) all cases. Another way to achieve similar effects to a merged cell is to use the camera tool. - juanito

Hello David - I think the oddness in your formulas arises because you count all the way to the end, and then backwards again. Wouldn't it be better just to count forwards? I attach an alternative that does just that. Note that I created some named formulas, including one for the total number...