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.
For unsubscribe instructions e-mail to: firstname.lastname@example.org
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy