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

On 03/26/2013 04:56 AM, Brian Barker wrote:
> At 23:30 25/03/2013 +0100, Krunoslav Šebetic wrote:
> > 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. :D


> > 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!
>
> At 00:32 26/03/2013 +0100, Krunoslav Šebetic wrote:
> > 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.

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