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

On 2013-03-26 12:32, Krunoslav Šebetić wrote:
> On 03/25/2013 11:55 PM, Steve Edmonds wrote:
> > 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)
>
>
> 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...)
>
>
> > > 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.
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

--
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