Single formula to return multiple column averages?

English
#1

I have a felling there is no way around writing a formula for each column group) rather than one formula that would return multiple values, but

I am working ion color analysis and whether RGB or Lab*, there will always be 3 values per color. I have blocks of data of varying size that I need the averages which become the centroid values to generate ∆E (didtsnver between each data set and the centroid set.

Just curious if there might be a single formula to accomplish this?

Thanks in advance (and I hope this isn’t too sill of a question)

Stan

#2

Average takes up to 30 arguments. =AVERAGE(B1:B99;D1:D99;F1:F99)

#3

I tried this.=AVERAGE(A22:A25,B22:B25,C22:C25). I used semi-colons, but it changes them to commas, and it appears it returns a sum total average, raster than 3 sets of values

#4

Please attach an example of what you expect to be returned, using this button:
image
because your request is not clear for me. If you want to return 3 values, then it is easier to have three formulas in three different cells in my opinion.

#7

RGB has HEX format, thus 256×256×256 (00…FF) constellations!

#9

Steph1. I am beginning to agree with you!

#13

I’m sure I didn’t understand anything here, but years ago I was interested for a short time in color models and neighborhoods of colors.
I will attach a Calc document from that time for which I wrote a few UDFs in Basic. At least one of them creates output to 3 cell wide strips of values. See columns E through G in the sheet myColors. In the example the used function returns the separated RGB values for the background of a given cell, but you can, of course, also return any differently calculated triple or tuple to a similar strip.
Please don’t ask me about anything related to the color models. I forgot everything about that. I may be able to answer an additional question about the structure of the sheets and the code.
ColorModelConversionExp.ods (25.6 KB)
Edited. See my post below.

#15

Thank you Lupp I have downloaded and will definitely take a look! thank you for sharing

Cheers
StainlessaB

#16

Hello Lupp, unfortunately the file opens with errors in most cells. Initially there is a run-time issue that finally resolves. All that is readable is the color names and the color samples… Perhaps in incompatibility issue with older versions of Libre. (?)

#17

Very strange: I had tested the old attachment under my standard user profile with the same Version (25.2.2.2) and it worked as expected. Having read your post I supposed a dependency on the Basic Standard Library of that profile, but I couldn’t find one. Using my “clean” profile, however, I got the errors you reported. Now I moved a single statement to a module without VBAsupport - and it worked. No success trying to understand that.
Reworked version attached:
ColorModelConversionExp_N.ods (19.7 KB)
though I don’t expect it will be very useful, for you.

#18

Hello, Wolfgang!
Here is the bug you found in its pure form. I think it’s better to report it.
TestRed.ods (10.0 KB)

#19

Sorry. I can’t see a bug here.
Cf:
TestRedReply.ods (17.8 KB)
My “strange” experiences were exactly as I descibed them above. I probably didn’t clearly emphasize that both tests mentioned above were done with user code under VBAsupport.

#20

I tried to open TestRed.ods again on a “clean” profile:

BASIC runtime error.
'35'
Sub-procedure or function procedure not defined.
Additional information: Red

Version: 24.8.6.1 (X86_64) / LibreOffice Community
Build ID: 051bf11303684a0a982c9966e8be766d0a9efbc7
CPU threads: 6; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Vulkan; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL threaded

#21

That’s my bug in V25.2.2.
And strange isn’t that Excel VBA has no Red(), or that it changes the working of some functions/commands as compared to native LibO Basic. The strange thing is that the same code works as expected in my “unclean” (complex) profile despite the fact that there also no Red() function is declared anywhere.

#5

… in CMYK you will define 4 basic “colours” in a wider spectrum as RGB. Do you want to analyse the reduced size of a cheap monitor or a special one with high color fidelity or the logarithmic sensory system of human perceptions of 18 billion different eyes in variations of the black and white seeing highly sensitive night light photoreceptors (contrast) to the high luminosity perceiving day light photoreceptors (color)?
In all there are no linear skalings! So what and/or witch you want to get a average value of what and/or why?
I think you’re trying to simplify an extremely complex topic somehow? I’m hoping for a system that takes into account people with different color orientations, those with reduced/increased red and/or green and/or blue and/or orange vision. The topic is anything but simple. I did not know anything about color average formulas!

#8

Koyotak. I am analyzing Lab* data acquired from raw data using flatbed scanner (lots of documents uses for this, Scatnner is IT8 calibrated and monitors are HD on a new Mac OSX15 system. I am studying the inks used in printing late 1800/early 1900 postage stamps. Lab* is my preference. The averaging is of clusters for centroid so I can plot spheres with a radii = to 2 ∆E. Using the K-means functions a starting point but it does not handle outliers well. the 3D scatter app is python based, which I built myself as I could not find anything that specifically addressed what I get were my needs.

I am retired, so if it turns out for naught, it was at least a mostly enjoyable use of my time.

Cheers

#10

I cannot know what you are knowing. Therefore:
Please specify the problem so that non-specialists can solve it without your very specific expertise. Typically:

  • given are…
  • sought are…
    …with an example of numerical value ranges.

I can’t derive a solution/algorithm from your babble!

Info
color quantities: saturation, chromaticity, brightness, gradation, hue, reflection or light source

#11

Thanks Koyotak

II was looking to see if there was a single formula that would return multiple results in multiple cells. It does not appear there is one, so a macro tat’s already written will be adapted.

Cheers

#12

Are you working with the same file that shared at Sorting data in Calc that has empty cells that need to remain? - #5 by StainlessB?
Which are the source cells?

#14

yes, LeroyG, that would be me,

Let me try and explain. The source cells are an average of one pair of sampled data sets. A left and a right, which are sample at the same relative location. Because it is known that the sampled pair was produced on the same day, time and machine (press), average is taken, and then plotted in a 3D scatter plot. To maintain a connection to the paired data with the average, there results an empty cell every other row.

I have since just copied the column of averaged data to another row and sorted which removes the blank row/cells

The blank row/cells created issues with the K-means and E functions in the python code…

This is perhaps more than you wished yo know. if so I apologize

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.