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

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

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

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


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