[Solved] MIN/MAX/AVERAGE/MEDIAN of binary numbers

[PAB]

Good evening,

I am having a problem with finding the MIN, MAX, and MEDIAN of a formula generated Binary List from a Lexicographic Index Number. Actually it sounds a lot harder than it is.

I have a list of formula generated Lexicographic Index Numbers [ Combination Sequence Numbers ] in cells DK16:DK3100. I have successfully generated the Binary Number for each of these. The Binary Number cells are in DM16:DM3100 and are formatted for 24 numbers [ for the respective zeros and ones of course ].

So far so good as that all works great.

I want to find the MIN, MAX, and MEDIAN of the range DM16:DM3100. I have used the below ARRAY formulas but just can't seem to get it to work as the MIN, MAX, and MEDIAN values all show 000000000000000000000000. The ARRAY formulas I am using are . . .

Code: Select all

In cell DM5: =MIN(IF(ISNUMBER(LEFT(DM$16:DM$3056;24)+0);LEFT(DM$16:DM$3056;24)+0))
In cell DM7: =MAX(IF(ISNUMBER(LEFT(DM$16:DM$3056;24)+0);LEFT(DM$16:DM$3056;24)+0))
In cell DM9: =MEDIAN(IF(ISNUMBER(LEFT(DM$16:DM$3056;24)+0);LEFT(DM$16:DM$3056;24)+0))

The strange thing is, these formulas work OK [ obviously with slight formula adjustments for each software ] in Excel and in LibreOffice Calc !!!

I think I have got to the stage where I can't see the wood for the trees.

Thanks in advance for any help. It is appreciated.

[MrProgrammer]

The Binary Number cells are in DM16:DM3100 and are formatted for 24 numbers [ for the respective zeros and ones of course ]. I want to find the MIN, MAX, and MEDIAN of the range DM16:DM3100.

Use these array formulas. MIN, MAX, and MEDIAN return decimal numbers, of course. You can use the BASE function to convert to binary, if needed.
=MIN(DECIMAL(DM16:DM3100;2))
=MAX(DECIMAL(DM16:DM3100;2))
=MEDIAN(DECIMAL(DM16:DM3100;2))

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet document.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[PAB]

Thanks for the reply MrProgrammer, it is appreciated.

I have put together the following cut down for size reasons spreadsheet as advised to make it simpler and more understandable as to what my problem is.

I have used only values in column B as opposed to the actual formulas used for obvious reasons.

Thanks again.

[karolus]

Hallo
just use:

Code: Select all

=LEFT(BASE(B5;2;24);24)

and copy down to the whole Range

AOO_112502.ods

(55.73 KiB) Downloaded 137 times

[PAB]

Thanks karolus, that worked perfectly.

As usual, I made it far too complcated for myself !

I just needed to change the 24 to 26 in the formula for the big numbers.

You have made an old man very happy.

Thanks again and enjoy the rest of your weekend.

[Alex1]

Then you don't need the LEFT function anymore.
The numbers in column B smaller than 12,000,000 aren't integer numbers but are displayed rounded, so their binary equivalent in column C doesn't necessarily match their rounded value.

[PAB]

Thanks for the reply Alex1,

Unfortunately, my little grey cells and I don't understand what you mean. The numbers in column B can go as high as 60 million.

Are you saying that some of the formulas are giving the incorrect Binary figures ?

Thanks again.

[MrProgrammer]

Are you saying that some of the formulas are giving the incorrect Binary figures ?

In your attachment cell B27 has value 1974361.99999999 which is displayed in the cell rounded to the integer 1,974,362 (an even decimal number) because your number format is #,##0 (no decimal places). Cell D27 has value 000111100010000001011001 which is an odd binary number because the base conversion functions ignore any fractional value like .99999999 in this case. Thus calculations done with the decimal values (like 1974361.99999999) may not match calculations done with the binary values (like 000111100010000001011001 → 1974361). Perhaps the unspecified formulas which produced the values in column B should be using one of the rounding functions in Calc so the cells contain integer values.

The numbers in column B can go as high as 60 million.

Exact base conversion depends on being able to store the value as an integer. Integers less than 1,000,000,000,000,000 will convert correctly. But 60 million will need 26 binary digits. The largest number you can store with 24 binary digits is 2²⁴-1 = 16,777,215. The largest number you can store with 26 binary digits is 2²⁶-1 = 67,108,863. 69,999,999 would need 27 binary digits.

[PAB]

Thanks for your input MrProgrammer, it is appreciated.

I have created another file including the actual formulas for the calculation of the Lexicographic Index Numbers [ Combination Sequence Numbers ]. I have added a ROUNDING parameter to that formula so when the Lexicographic Index Numbers are copied, they are copied as a WHOLE numbers.

I have not done anything with the fore-mentioned LEFT parameter as I am not quite sure what to do.

Once again, thank you everybody for your input and help.

[Alex1]

A function that gives the left 26 characters of a 26 character string just returns the whole string.
For the average, the binary value still doesn't correspond with the rounded decimal value, so it's better to apply the rounding in column K than in column I, e.g. in K16: =BASE(ROUND(I16);2;26)

[PAB]

Thanks for the explanation Alex1.

I would just like to thank EVERYONE for their input, advice, and help, it is very much appreciated.