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