Help with an Excel formula, please !

[p45cal]

MS 365?
Try:

Code: Select all

=LET(aa,A1:C1,a,INDEX(aa,,1),b,INDEX(aa,,2),c,INDEX(aa,,3),aa+XLOOKUP(VALUE(IF(a>b,2,IF(a=b,1,0))&IF(a>c,2,IF(a=c,1,0))&IF(b>c,2,IF(b=c,1,0))),{0;1;2;12;22;100;111;122;200;210;220;221;222},{2.64,-2.4,-0.2;0.08,2.74,-2.8;2.64,5.3,-7.9;-0.3,2.4,-2.1;-6.1,5.3,-7.9;-0.3,3.4,-3.1;-0.6,3.07,-2.5;-0.9,2.74,-1.8;-6.1,6.3,-0.2;-0.9,3.74,-2.8;1.64,6.3,-7.9;-1.3,3.4,-2.1;1.64,-2.4,0.75}))

2023-04-11_150754.png

[HansV]

Here it is complete with the additions.

[SpeakEasy]

Or ...

fixed values2.xlsx

[Olimpiu]

Not working. I don't know what is happening. Is hard to believe that every reply is not working. When I click on a cell with formula, whatever I do...I can not undo. The only way is to delete the task from task manager ( CTRL +ALT+DEL).
If I continue adding new values, in the right apears "#Name?"

[HansV]

Which version of Excel do you use?

[Olimpiu]

I think there is no solving for this task in excel.
The only way is to do it manually looking at the helper like in " fixed values2.xlsx"
Thank you all for the time !

[Olimpiu]

Microsoft Excel 2010

[SpeakEasy]

>there is no solving for this task in excel

Er ... I see at least 3 Excel solutions ... 2 of them use a helper column,. which is a tried and tested technique in Excel. The third (from p45cal) gives the superficial appearance of not using one, but in fact builds it into the array function that they use (and sadly won't work in Excel 2010; as p45cal suggests, it only works in M365 and Office 2021)

[Olimpiu]

I will find a way to solve it. Thank a lot for your efforts !

[snb]

Maybe this is what you are after:

[SpeakEasy]

That's unfortunately not the same thing at all ...

[Olimpiu]

No. In the columns BCD Insert 3 numbers. I need to do this for hundreds of rows ( downway)
The columns FGH have to calculate in paralel by the rules, adding to corespondent cel from BCD, a value.
The rule is the problem. It logical, but the excel can do it
I attached another file.
BCD - here I insert numbers.
In B5 is 1, In C5 is 3, In D5 is 2
This row of 3 numbers have a specific. First number ( B5) is smaller than C5, Also, B5 is smaller than D5. And D5 is bigger than B5 ( 1,3,2)
This row have a specific that respects the template from the "Values templates" block - row V14, W14, X14. This is the only templates that fits The 132.
In B6, C6, D6 I have 8,4,6. This row format fits only in the template from V10, W10, X10
I did the addings manually.
If I do this manually for 1500 rows, i need days, so, the automatization is the solution.
For example, if I put numbers ( any numbers) in the row B7,C7,D7 the excel must know the format of the row and fit it with the apropriate template from the "Values template" and calculate itself in the FGH

[HansV]

This then?

addcel.xlsb

[snb]

Next version: