Summing a cell value by a backgroud color cell style

users
#1

Hello,

I need to make a Calc table. Table is "structured" verticly. Tables sums number values from collums in the last row of table. Every collum stands for itself, and has nothing do do with oder collums.

But values are enterd in collum by different variables. I have set different cell background color for every variable. Is there a way to tell Calc to sum, devide or multiply values by the cell style color?

I can not enter summed, devidded or multiplied values directly to collum because I need to initials values summed (for each collum) so I want to set some kinde of formula to a different place in sheet with modified values. Maybe it can be done with sumif?

Maybe I am doing it wrong, maybe it can be done different way?

Sory for bad english, I can explain more in depth if necessary.

Kruno

#2

Hi :slight_smile:
If you want to upload the file so that everyone can see it then Nabble is a
good place. When you are in the right thread in Nabble click on "Reply".
Above where you can type in are a few buttons. The "More" button's top
option is "Upload file" which works roughly the same as uploading an
attachment to an email except that it puts an html link into the message
box. You can move the link around to place it sensibly if you want.
Regards from
Tom :slight_smile:

#3

Have problem with Nabble, or should I say problem with Firefox (Iceweasel as packed with Debian Linux) - can not see field to register and I can not see some other text in the page. Think is nothing wrong with Nabble because I have this problem for a while. But I will send the example file to you, so maybe you could upload it to Nabble.

Let me now if you got the attachment - simple 11 kb ods file. Hope that with file the whole question makes more sense.

#4

Not that I know of. But there is an easier way. I'm guessing that you have set the background colours manually. Instead of doing that, why not create another column to indicate the condition (I'm not sure what you mean by "entered ... by different variables")? You can then use the values in the new column to control the SUMIF() to form your selective sums. But you can also use the values in the new column in "conditional formatting" to set the background colours of the cells in the first place. This is a much more reliable technique.

If you wish, the extra columns can be hidden or displaced to another part of the sheet or even to another sheet, so they do not need to be immediately visible or to print along with the values you do need to see. It may not even be necessary to create new columns, if the conditions they need to contain could be derived directly from whatever is their source.

I trust this helps.

Brian Barker

#5

Hi :slight_smile:
Here is the uploaded file

example.ods <http://nabble.documentfoundation.org/file/n4046112/example.ods>

I am also adding a screen-shot of how Nabble looked, with my mouse just
beside the "More" button so you can see if IceWeasel really is misbehaving
30.png <http://nabble.documentfoundation.org/file/n4046112/30.png>
Normally i would make it into an indexed Gif and crop&resize it to make it
smaller but i'm right in the middle of a LiveCd session and don't have room
for Gimp here.
Apols and regards from
Tom :slight_smile:

#6

I need to make a Calc table. Table is "structured" vertically. Tables sums number values from columns in the last row of table. Every column stands for itself, and has nothing to do with other columns. But values are entered in column by different variables. I have set different cell background color for every variable. Is there a way to tell Calc to sum, divide or multiply values by the cell style color?

Not that I know of. But there is an easier way. I'm guessing that you have set the background colours manually. Instead of doing that, why not create another column to indicate the condition (I'm not sure what you mean by "entered ... by different variables")? You can then use the values in the new column to control the SUMIF() to form your selective sums. But you can also use the values in the new column in "conditional formatting" to set the background colours of the cells in the first place. This is a much more reliable technique.

Lets say I am collecting wether temperature (but Im not) values and Im doing it five time a day. Lets say that every time (of five) I need to know is that (enterd) value above or bellow the average for this time a day, month and year. In that case I would have three type of (I call them) values which can not be predicted and thus i doubt I can set a secend - or the secend one would be bigger and far more complex that the primary one.

Now, imagine you need sum only the values (temperature) that is above average for this time of the year and belove average of that month. What is above the average of the year is not nessery below the average of the month and vice verse. So I must say that I am now shure if sumif can handle that. I do not need sum numbers higher or lower from x, I need sum number collected from the real world and those are quite rondom.

If you wish, the extra columns can be hidden or displaced to another part of the sheet or even to another sheet, so they do not need to be immediately visible or to print along with the values you do need to see. It may not even be necessary to create new columns, if the conditions they need to contain could be derived directly from whatever is their source.

I trust this helps.

Brian Barker

It helps, thank you. This sure is the way to go but it can get quite complicated with time.

#7

Hi :slight_smile:
Here is the uploaded file

example.ods <http://nabble.documentfoundation.org/file/n4046112/example.ods>

I am also adding a screen-shot of how Nabble looked, with my mouse just
beside the "More" button so you can see if IceWeasel really is misbehaving
30.png <http://nabble.documentfoundation.org/file/n4046112/30.png>
Normally i would make it into an indexed Gif and crop&resize it to make it
smaller but i'm right in the middle of a LiveCd session and don't have room
for Gimp here.
Apols and regards from
Tom :slight_smile:

Thanks for the upload and screenshot, I got it all white white, nothing to click. Again, its not Nabble, its my browser and operating system.

#8

I need to make a Calc table. Table is "structured" vertically. Tables sums number values from columns in the last row of table. Every column stands for itself, and has nothing to do with other columns. But values are entered in column by different variables. I have set different cell background color for every variable. Is there a way to tell Calc to sum, divide or multiply values by the cell style color?

Not that I know of. But there is an easier way. I'm guessing that you have set the background colours manually. Instead of doing that, why not create another column to indicate the condition (I'm not sure what you mean by "entered ... by different variables")? You can then use the values in the new column to control the SUMIF() to form your selective sums. But you can also use the values in the new column in "conditional formatting" to set the background colours of the cells in the first place. This is a much more reliable technique.

Lets say I am collecting wether temperature (but Im not) values and Im doing it five time a day.

Does this mean that you add 5 new lines each day. Do you add the date/time and the temperature 5 times a day. Do you ad 1 line each day with 5 temperatures in each line that are added during the day.
Are the times consistent real times or do you just call them time1 ...time5. (breakfast, morning tea, lunch, afternoon tea, dinner)

Lets say that every time (of five) I need to know is that (enterd) value above or bellow the average for this time a day, month and year.

So is the idea of the background colour is to show if that (enterd) value above or bellow the average for this time a day, month and year. This is 1 condition or 3 conditions
(above the average for this time of day AND above the average for this time of month AND above the average for this time of year)=Y
(below the average for this time of day AND below the average for this time of month AND below the average for this time of year)=N

or
(above the average for this time of day)Y/N
(above the average for this time of month )Y/N
(above the average for this time of year)Y/N

In that case I would have three type of (I call them) values which can not be predicted and thus i doubt I can set a secend - or the secend one would be bigger and far more complex that the primary one.

Now, imagine you need sum only the values (temperature) that is above average for this time of the year and belove average of that month.

Is that summing from the 5 values for that day or summing all the history

#9

I need to make a Calc table. Table is "structured" vertically. Tables sums number values from columns in the last row of table. Every column stands for itself, and has nothing to do with other columns. But values are entered in column by different variables. I have set different cell background color for every variable. Is there a way to tell Calc to sum, divide or multiply values by the cell style color?

Not that I know of. But there is an easier way. I'm guessing that you have set the background colours manually. Instead of doing that, why not create another column to indicate the condition (I'm not sure what you mean by "entered ... by different variables")? You can then use the values in the new column to control the SUMIF() to form your selective sums. But you can also use the values in the new column in "conditional formatting" to set the background colours of the cells in the first place. This is a much more reliable technique.

Lets say I am collecting wether temperature (but Im not) values and Im doing it five time a day.

Does this mean that you add 5 new lines each day. Do you add the date/time and the temperature 5 times a day. Do you ad 1 line each day with 5 temperatures in each line that are added during the day.
Are the times consistent real times or do you just call them time1 ...time5. (breakfast, morning tea, lunch, afternoon tea, dinner)

I have hard time explaining such complicated thing in english and seems the wehter thing is a bad exemple. Tom uploaded file I sent him (http://nabble.documentfoundation.org/file/n4046112/example.ods). All enteries are changeble - I enter one line (cell in collumn) per day. But if the value is higher then the year average and smaller then month average I put it green, if the value is lower then the year average and higher the month average I put in red, if the value is equal to year average and month average I put it blue. Now, if cell is colord red, I devide it by 2. If the cell is blue i multiply that value (in blue) with 1.5, and if green I do plus operation (x(blue) + y). There is no sorting in table, green cells are not on top, and blue ones are not at the bottom. If cell is (from exemple) colord green it still can be higher or lower then one in blue, so sumif _can_ do it wrang way.

The problem is that in my case there is now juist "year" and "month" plus permutations with "lower" and "higher", its match more complex than that. Coloring the cell background and posibillity do manipulating data based on that would make job lot more easyer.

Lets say that every time (of five) I need to know is that (enterd) value above or bellow the average for this time a day, month and year.

So is the idea of the background colour is to show if that (enterd) value above or bellow the average for this time a day, month and year. This is 1 condition or 3 conditions

If below from average of this time a the day, month and year - color x. If above from average of this time a the day, month and year - color y. If below of this time a day but higher of average of month and higher of average of the year - color z. If below of this time a day but higher of average of month and _this time_ lower than the average of the year - color a....

Hope this helps.

(above the average for this time of day AND above the average for this time of month AND above the average for this time of year)=Y
(below the average for this time of day AND below the average for this time of month AND below the average for this time of year)=N

or
(above the average for this time of day)Y/N
(above the average for this time of month )Y/N
(above the average for this time of year)Y/N

In that case I would have three type of (I call them) values which can not be predicted and thus i doubt I can set a secend - or the secend one would be bigger and far more complex that the primary one.

Now, imagine you need sum only the values (temperature) that is above average for this time of the year and belove average of that month.

Is that summing from the 5 values for that day or summing all the history

Its summing values colored with color x in cell A1, summing values colored with color y in cell A2 ... and finaly summing all together in B1 (color x + color y + color z...)

#10

Hi.
From the example on nabble I can see what you are doing but not how it relates to your example above. There are 3 columns and not 5, is this 3 times a day. There is no date or time, is it to be assumed that all data in column A is at the same time and each line is a new day.

Steve

#11

Let's say I am collecting weather temperature (but I'm not) values ...

So let's not say that, then! Why not tell us what you are doing - or just call them "values"?

... and I'm doing it five time a day. Let's say that every time (of five) I need to know is that (entered) value above or below the average for this time a day, month and year. In that case I would have three type of (I call them) values which can not be predicted ...

If - as is now evident - the calculations you want to do require knowledge of those averages, they also need to be included in the spreadsheet as a separate table. Then you can leave your calculations to the spreadsheet - which is the whole idea of them, of course.

... and thus I doubt I can set a second - or the second one would be bigger and far more complex that the primary one.

Sorry: I don't understand this bit.

Now, imagine you need sum only the values (temperature) that is above average for this time of the year and below average of that month. [...] So I must say that I am now sure if sumif can handle that.

Provided you have the various average values listed in the spreadsheet, the SUMIF() function can easily achieve what you need.

I do not need sum numbers higher or lower from x, ...

Er what's "x", please?

... I need sum number collected from the real world and those are quite random.

You are tying yourself in knots here: no-one is suggesting that your values should not be "random", as you put it. But if you need to do calculations that depend on certain values, those values (in this case your averages) need to appear in your spreadsheet. Otherwise you are asking Calc to read your mind!

I have hard time explaining such complicated thing in English ..

You shouldn't underplay your skills in English: what you have explained is quite understandable (although there are some things that you haven't said).

... and seems the weather thing is a bad example.

Yes: why not just describe what you are *actually* doing?! (There is no need to divulge anything confidential.)

Tom uploaded file I sent him ... . [...] But if the value is higher then the year average and smaller then month average I put it green, if the value is lower then the year average and higher the month average I put in red, if the value is equal to year average and month average I put it blue.

A quick look at this confirms, I think, what I suggested in a previous message. What you are doing is to compare your input values to other values - not currently in the spreadsheet - and to colour the cells according to the result of your mental arithmetic. That comparison is something that spreadsheets do easily and reliably but humans less so. So that is a bad move: let the spreadsheet do this for you. You are also choosing to encode the result of these comparisons as colours of cells - something that is immediately meaningful to the human eye but very difficult for the spreadsheet to use!

Now, if cell is colored red, I divide it by 2. If the cell is blue i multiply that value (in blue) with 1.5, and if green I do plus operation (x(blue) + y).

I can't see these aspects in the sample spreadsheet you submitted. Do you mean that you applied these calculations *before* you entered the data? (I hope not.)

Here is what I suggest:
o Enter all your raw data into the spreadsheet. This includes the averages (or whatever they are) that you are currently using (in your head) to determine which colour the cells should have.
o Evaluate what you currently have as colours within the spreadsheet, either within other formulae or as values in additional columns.
o You appear to need the sum of a series of products to calculate what you need, so SUMIF() is probably not the best way. Instead, you may find SUMPRODUCT() useful, but this depends on exactly how you design your sheet.

Note that these suggestions do not need you to colour the cells at all. If you still decide to colour them, I recommend that you do so using "conditional formatting" (found in the Format menu), based on values you will now have in the spreadsheet. This will be more reliable that manual formatting.

Note also that, if you don't want to see all the additional values either on screen or on printout, you can avoid this is various ways:
o You can hide the relevant columns.
o You can move them out of your print ranges.
o You can move them to other sheets.

I trust this helps.

Brian Barker

#12

Well that ods file is just exempla, as I mentiond before. I do not have document created. This is just a preparation and because of that file from nabble is a scheme. Thats the reasen Im using this mailing list. Just wonna know is posible add values/numbers based on cell background color.

We can say that Collum A is one day and collum B is another. We can say thats needed the rows represents time of the day (morning, noon, evening). So, first I need to fill collumn A and rows respective to the collumn, and onother day rows resprective to collumn B.

This wether thing is the colosest to what Im doing, and i really doubt I could explane it on the real project. Is it possible do edit xml file or something and do it with some find/repleca - if in xml file founded line "background color:red" "devide by 2.7" and put that number betwen string x and string y?

Thanks for your time!

#13

Let's say I am collecting weather temperature (but I'm not) values ...

So let's not say that, then! Why not tell us what you are doing - or just call them "values"?

Need to messure voltage and staff from battery. But I tend to connect various additions to it. With every addition the expected average value is different. Collum A is actuali addition 1, collum B is addition 2 and so on...

... and I'm doing it five time a day. Let's say that every time (of five) I need to know is that (entered) value above or below the average for this time a day, month and year. In that case I would have three type of (I call them) values which can not be predicted ...

If - as is now evident - the calculations you want to do require knowledge of those averages, they also need to be included in the spreadsheet as a separate table. Then you can leave your calculations to the spreadsheet - which is the whole idea of them, of course.

... and thus I doubt I can set a second - or the second one would be bigger and far more complex that the primary one.

Sorry: I don't understand this bit.

Values for the secend table can expand - start with 5 average values, in 2 months there can be 12 values, or the original five can change. New values needs to replace old ones for coresponding condition but should not erese or recalculate values calculated with old (original) 5. :smiley:

Now, imagine you need sum only the values (temperature) that is above average for this time of the year and below average of that month. [...] So I must say that I am now sure if sumif can handle that.

Provided you have the various average values listed in the spreadsheet, the SUMIF() function can easily achieve what you need.

I do not need sum numbers higher or lower from x, ...

Er what's "x", please?

X is rondom number.

... I need sum number collected from the real world and those are quite random.

You are tying yourself in knots here: no-one is suggesting that your values should not be "random", as you put it. But if you need to do calculations that depend on certain values, those values (in this case your averages) need to appear in your spreadsheet. Otherwise you are asking Calc to read your mind!

I have hard time explaining such complicated thing in English ..

You shouldn't underplay your skills in English: what you have explained is quite understandable (although there are some things that you haven't said).

... and seems the weather thing is a bad example.

Yes: why not just describe what you are *actually* doing?! (There is no need to divulge anything confidential.)

Tom uploaded file I sent him ... . [...] But if the value is higher then the year average and smaller then month average I put it green, if the value is lower then the year average and higher the month average I put in red, if the value is equal to year average and month average I put it blue.

A quick look at this confirms, I think, what I suggested in a previous message. What you are doing is to compare your input values to other values - not currently in the spreadsheet - and to colour the cells according to the result of your mental arithmetic. That comparison is something that spreadsheets do easily and reliably but humans less so. So that is a bad move: let the spreadsheet do this for you. You are also choosing to encode the result of these comparisons as colours of cells - something that is immediately meaningful to the human eye but very difficult for the spreadsheet to use!

Now, if cell is colored red, I divide it by 2. If the cell is blue i multiply that value (in blue) with 1.5, and if green I do plus operation (x(blue) + y).

I can't see these aspects in the sample spreadsheet you submitted. Do you mean that you applied these calculations *before* you entered the data? (I hope not.)

Here is what I suggest:
o Enter all your raw data into the spreadsheet. This includes the averages (or whatever they are) that you are currently using (in your head) to determine which colour the cells should have.
o Evaluate what you currently have as colours within the spreadsheet, either within other formulae or as values in additional columns.
o You appear to need the sum of a series of products to calculate what you need, so SUMIF() is probably not the best way. Instead, you may find SUMPRODUCT() useful, but this depends on exactly how you design your sheet.

Note that these suggestions do not need you to colour the cells at all. If you still decide to colour them, I recommend that you do so using "conditional formatting" (found in the Format menu), based on values you will now have in the spreadsheet. This will be more reliable that manual formatting.

Note also that, if you don't want to see all the additional values either on screen or on printout, you can avoid this is various ways:
o You can hide the relevant columns.
o You can move them out of your print ranges.
o You can move them to other sheets.

I trust this helps.

Brian Barker

I will do what you said, its look like I tend do things wrang way. I will tray with SUMPRODUCT () and with SUMIF and see which way suits for me. It looks like I just need to plan the whole thing a little bit better.

Thank you, Brian, for your time.

#14

Hi Kruno,

Krunoslav Šebetić schrieb:

Hello,

I need to make a Calc table. Table is "structured" verticly. Tables sums
number values from collums in the last row of table. Every collum stands
for itself, and has nothing do do with oder collums.

But values are enterd in collum by different variables. I have set
different cell background color for every variable. Is there a way to
tell Calc to sum, devide or multiply values by the cell style color?

No, there is no build-in function to get the color of a cell. You need to write a macro. The background color is the property "CellBackColor". It is set, when you color the cell directly and when you color it by style as well.

You can use macro functions in nearly the same way as the build-in functions.

I can not enter summed, devidded or multiplied values directly to collum
because I need to initials values summed (for each collum) so I want to
set some kinde of formula to a different place in sheet with modified
values. Maybe it can be done with sumif?

Maybe I am doing it wrong, maybe it can be done different way?

I would not put such information as color to the cell, but I would use a separate, parallel column, which only contains these flags; not as color but as values, which you can use directly in a build-in function.

Kind regards
Regina

#15

Hi :slight_smile:
I would definitely use more columns for this.  Possibly in groups of 3 perhaps with an extra column as an empty gap to make it easier to read.  Perhaps change "Page layout" to 'landscape' instead of 'portrait' in case i needed to print it out.

So, something like

Column A = time, for it's heading i would use the date or the day-of-the-week

Column B = value, for it's heading i would say something like "Imported data"  (perhaps turned sideways or something?)Column C = calculated code (instead of colour), for it's heading i would say "code" 
Column D = calculated value, for it's heading i would say something like "Result"
Column E = blank but narrowed to half width or less.  It's often handy to have an extra column "just in case" but it's main purpose is to split the groups of columns up

Column F = time, for it's heading i would use the date or the day-of-the-week

Column G = value, for it's heading i would say something like "Imported data"  (perhaps turned sideways or something?)
Column H = calculated code (instead of colour), for it's heading i would say "code" 
Column I = calculated value, for it's heading i would say something like "Result"
Column J = blank but narrowed to half width or less.  It's often handy to have an extra column "just in case" but it's main purpose is to split the groups of columns up

The currently uploaded file's layout looks a bit random so it might help to make it more formal even though that takes a bit of the magic out of it, as does losing the colours and replacing with "Code".

Regards from
Tom :slight_smile:

#16

Nice - I really could use numbers in different collumn insted of colors - thats practicly it.

#17

Hi :slight_smile:
I would definitely use more columns for this. Possibly in groups of 3 perhaps with an extra column as an empty gap to make it easier to read. Perhaps change "Page layout" to 'landscape' instead of 'portrait' in case i needed to print it out.

So, something like
Column A = time, for it's heading i would use the date or the day-of-the-week
Column B = value, for it's heading i would say something like "Imported data" (perhaps turned sideways or something?)
Column C = calculated code (instead of colour), for it's heading i would say "code"
Column D = calculated value, for it's heading i would say something like "Result"
Column E = blank but narrowed to half width or less. It's often handy to have an extra column "just in case" but it's main purpose is to split the groups of columns up

Column F = time, for it's heading i would use the date or the day-of-the-week
Column G = value, for it's heading i would say something like "Imported data" (perhaps turned sideways or something?)
Column H = calculated code (instead of colour), for it's heading i would say "code"
Column I = calculated value, for it's heading i would say something like "Result"
Column J = blank but narrowed to half width or less. It's often handy to have an extra column "just in case" but it's main purpose is to split the groups of columns up

I will try that in Calc.

The currently uploaded file's layout looks a bit random so it might help to make it more formal even though that takes a bit of the magic out of it, as does losing the colours and replacing with "Code".

Regards from
Tom :slight_smile:

Well, it is rondom, it a scheme for demonstration purpos.

#18

Hi :slight_smile:
Did anyone ask which OS and which web-browser?  There are some web-browsers that work only on the command-line and use Ascii to make images and stuff.  Very light-weight and fun but a bit impractical.  Plus they probably don't have a Windows version.  Scaling up a bit i bet there is a light-weight web-browser that you could install and have as an occasional alternative.  I tend to have 2 or 3 web-browsers on a machine jic i suddenly need to check something. 
Regards from
Tom :slight_smile:

#19

Hi :slight_smile:
Ooops!  Sorry about asking this on-list.  It's an old thread now that has been solved already and i was just wondering about a couple of side issues.  I've been told off-list and it looks like that issue got fixed already too :slight_smile:
Apols for the traffic!
Regards from
Tom :slight_smile:

________________________________
From: Tom Davies <tomdavies04@yahoo.co.uk>
To: Krunoslav Šebetić <kruno0407@gmail.com>; "users@global.libreoffice.org" <users@global.libreoffice.org>
Sent: Tuesday, 23 April 2013, 19:15
Subject: Re: [libreoffice-users] Re: Summing a cell value by a backgroud color cell style

Hi :slight_smile:
Did anyone ask which OS and which web-browser?  There are some web-browsers that work only on the command-line and use Ascii to make images and stuff.  Very light-weight and fun but a bit impractical.  Plus they probably don't have a Windows version.  Scaling up a bit i bet there is a light-weight web-browser that you could install and have as an occasional alternative.  I tend to have 2 or 3 web-browsers on a machine jic i suddenly need to check something. 
Regards from
Tom :slight_smile:

________________________________
From: Krunoslav Šebetić <kruno0407@gmail.com>
To: users@global.libreoffice.org
Sent: Monday, 25 March 2013, 22:34
Subject: Re: [libreoffice-users] Re: Summing a cell value by a backgroud color cell style

Hi :slight_smile:
Here is the uploaded file

example.ods <http://nabble.documentfoundation.org/file/n4046112/example.ods>

I am also adding a screen-shot of how Nabble looked, with my mouse just
beside the "More" button so you can see if IceWeasel really is misbehaving
30.png <http://nabble.documentfoundation.org/file/n4046112/30.png>
Normally i would make it into an indexed Gif and crop&resize it to make it
smaller but i'm right in the middle of a LiveCd session and don't have room
for Gimp here.
Apols and regards from
Tom :slight_smile:

Thanks for the upload and

screenshot, I got it all white white, nothing