2013 Archives by date, by thread · List index

# 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, ...
```
```

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