Re: [libreoffice-users] Summing a cell value by a background color cell style

On 2013-03-26 11:30, Krunoslav Šebetić wrote:
> On 03/25/2013 11:08 PM, Brian Barker wrote:
> > At 15:16 25/03/2013 +0100, Krunoslav Šebetić wrote:
> > > 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
> 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.


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted