Generate round to nearest 2 interval (text) from 2 values

[ooptennoort]

Dear experts,
I am trying to convert an excel formula into M for PQ but I think it cannot be done. I need to generate an interval (of 2's) from 2 values.

First	Second	Great result	Ideal Result
2.5​	12.5​	6-8	2-4;4-6;6-8;8-10;10-12;12-14
4.2​	13.2​	8-10	4-6;6-8;8-10;10-12;12-14
8​	1​	4-6	0-2;2-4;4-6;6-8
0.1​	4.9​	2-4	0-2;2-4;4-6
116​	117​	116-118	116-118
34​	34​	34-36	34-36
32.3​	32.3​	32-34	32-34
5​	13​	8-10	4-6;6-8;8-10;10-12;12-14

The 3rd column would be great. (Did not even attempt the 4th column.)
This was my excel formula:
=IF([@Second]=0,MROUND([@First],2)&"-"&(MROUND([@First],2)+2),MROUND(MAX(0,AVERAGE([@First],[@Second])-1),2)&"-"&MROUND((AVERAGE([@First],[@Second])+1),2))

This is what I produced so far (when I ran into errors, esp. because of &):
= Table.AddColumn(Add2Interval, "2Interval", each if [Second]=0 then Number.ToText(RoundingMode.ToEven([First]/2)*2)
else if [Second]=[First] then [First]
else Number.ToText(RoundingMode.ToEven([Second]/2)*2-1)&"-"&Number.ToText(RoundingMode.ToEven([Second]/2)*2+1), type text)

Anyone?

 

[jasonb75]

I know nothing about M code, but I was bored so had a go at getting your ideal result from a formula.

Book1
	A	B	C
1	First	Second	Result
2	2.5	12.5	2-4;4-6;6-8;8-10;10-12;12-14
3	4.2	13.2	4-6;6-8;8-10;10-12;12-14
4	8	1	0-2;2-4;4-6;6-8
5	0.1	4.9	0-2;2-4;4-6
6	116	117	116-118
7	34	34	34-36
8	32.3	32.3	32-34
9	5	13	4-6;6-8;8-10;10-12;12-14
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=LET(f,FLOOR(MIN([@[First]:[Second]]),2),s,SEQUENCE((CEILING(MAX([@[First]:[Second]]),2)-f)/2+AND([@[First]:[Second]]=INT(MAX([@[First]:[Second]]))),,f,2),TEXTJOIN(";",1,s&"-"&s+2))

 

[ooptennoort]

I know nothing about M code, but I was bored so had a go at getting your ideal result from a formula.

Book1
	A	B	C
1	First	Second	Result
2	2.5	12.5	2-4;4-6;6-8;8-10;10-12;12-14
3	4.2	13.2	4-6;6-8;8-10;10-12;12-14
4	8	1	0-2;2-4;4-6;6-8
5	0.1	4.9	0-2;2-4;4-6
6	116	117	116-118
7	34	34	34-36
8	32.3	32.3	32-34
9	5	13	4-6;6-8;8-10;10-12;12-14
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=LET(f,FLOOR(MIN([@[First]:[Second]]),2),s,SEQUENCE((CEILING(MAX([@[First]:[Second]]),2)-f)/2+AND([@[First]:[Second]]=INT(MAX([@[First]:[Second]]))),,f,2),TEXTJOIN(";",1,s&"-"&s+2))

I know nothing about M code, but I was bored so had a go at getting your ideal result from a formula.

Book1
	A	B	C
1	First	Second	Result
2	2.5	12.5	2-4;4-6;6-8;8-10;10-12;12-14
3	4.2	13.2	4-6;6-8;8-10;10-12;12-14
4	8	1	0-2;2-4;4-6;6-8
5	0.1	4.9	0-2;2-4;4-6
6	116	117	116-118
7	34	34	34-36
8	32.3	32.3	32-34
9	5	13	4-6;6-8;8-10;10-12;12-14
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=LET(f,FLOOR(MIN([@[First]:[Second]]),2),s,SEQUENCE((CEILING(MAX([@[First]:[Second]]),2)-f)/2+AND([@[First]:[Second]]=INT(MAX([@[First]:[Second]]))),,f,2),TEXTJOIN(";",1,s&"-"&s+2))

Dear jasonb75,
Hats off for this... well... magic. I'll give it a try tonight.
That said, should anyone want to try achieving the same in PQ (M)... The drawback of VBA is that running this macro is an extra step after simply refreshing with PQ. And many of my co-workers don't recognise *.xlsm or how to run a macro. And then there's the security issue of VBA...
So hopefully Power Query is an option too

 

[ooptennoort]

Dear jasonb75,
Hats off for this... well... magic. I'll give it a try tonight.
That said, should anyone want to try achieving the same in PQ (M)... The drawback of VBA is that running this macro is an extra step after simply refreshing with PQ. And many of my co-workers don't recognise *.xlsm or how to run a macro. And then there's the security issue of VBA...
So hopefully Power Query is an option too

I don't see how to implement your function. I tried in VBA, in excel and in power query, but nothing seems to work. How do I do it?

 

[bebo021999]

I wonder if "Great results" is your final expection?

Book1
	A	B	C	D
1	First	Second	Result1	Result2
2	2.5	12.5	6-8
3	4.2	13.2	8-10
4	8	1	4-6
5	0.1	4.9	2-4
6	116	117	116-118
7	34	34	34-34
8	32.3	32.3	32-34
9	5	13	8-10
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=FLOOR(AVERAGE(A2,B2),2)&"-"&CEILING(AVERAGE(A2,B2),2)

If you still need "Ideal Results", I will try with VBA later.

 

[jasonb75]

In the worksheet, it's an excel formula the same as your

This was my excel formula:
=IF([@Second]=0,MROUND([@First],2)&"-"&(MROUND([@First],2)+2),MROUND(MAX(0,AVERAGE([@First],[@Second])-1),2)&"-"&MROUND((AVERAGE([@First],[@Second])+1),2))

 

[ooptennoort]

In the worksheet, it's an excel formula the same as your

That's what I thought, but I get this error. What am I doing wrong?

 

[ooptennoort]

I wonder if "Great results" is your final expection?

Book1
	A	B	C	D
1	First	Second	Result1	Result2
2	2.5	12.5	6-8
3	4.2	13.2	8-10
4	8	1	4-6
5	0.1	4.9	2-4
6	116	117	116-118
7	34	34	34-34
8	32.3	32.3	32-34
9	5	13	8-10
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=FLOOR(AVERAGE(A2,B2),2)&"-"&CEILING(AVERAGE(A2,B2),2)

If you still need "Ideal Results", I will try with VBA later.

My "need" is for my above excel formula to work in Power Query. This formula gives "good results" (in excel, so far only). Getting PQ to give comma seperated (or whatever) results in one cell would be Ideal (but extremely challenging)

 

[jasonb75]

Try this instead, I assume that your data is still formatted as a table, if it is not then it won't recognise the column names as valid references. I have seen before where table names don't copy correctly from the forum but it's usually only when there are spaces in the name and it gets split over 2 lines.

=LET(f,FLOOR(MIN([@[First]],[@[Second]]),2),s,SEQUENCE((CEILING(MAX([@[First]],[@[Second]]),2)-f)/2+AND([@[First]]=INT(MAX([@[First]],[@[Second]])),[@[Second]]=INT(MAX([@[First]],[@[Second]]))),,f,2),TEXTJOIN(";",1,s&"-"&s+2))

If that still fails, try changing [@[First]] to A2 and [@[Second]] to B2 then it should accept it but I don't see any reason why the first one has given the error.

 

[ooptennoort]

=LET(f,FLOOR(MIN([@[First]],[@[Second]]),2),s,SEQUENCE((CEILING(MAX([@[First]],[@[Second]]),2)-f)/2+AND([@[First]]=INT(MAX([@[First]],[@[Second]])),[@[Second]]=INT(MAX([@[First]],[@[Second]]))),,f,2),TEXTJOIN(";",1,s&"-"&s+2))

Wow! (And now I see where I went wrong: indeed, tested on copy of data+formula from form, which isn't in table format.)
This is amazing! It's not in M but it is a semi-solution in that I can have PQ add this formula as text in a column and load it to excel and then click it to turn it into a formula. (Still less clicking than looking up the formula, copying it, pasting it into the cells and filling down).
Mind if I wait if someone can/will address the PQ challenge (before I mark as solution)?!