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

On 03/26/2013 01:05 AM, Steve Edmonds wrote:
> 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

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!


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