At 19:11 10/10/2013 +0200, Hylton Conacher wrote:
I have a spreadsheet with four pricing ranges for a certain number of units.
G4 = 601
H4 = calculated by below formula = 751.25 but should be 751.52
.
F20 = 1.25
G20 = 150 i.e. 0->150 units
h20 = F*G
I20 = 1.25
J20 = 200 i.e. 150.0001 -> 350 units
K20 = I*J
L20 = 1.25
M20 = 250 i.e. 350.0001 -> 600 units
N20 = L*M
O20 = 1.52
P20 >= 600 i.e. 600.0001 -> infinity units
Q20 = O*P
The below formula calculates H4
=IF(0<G4<=$G$20,G4*$F$20,IF($G$20<G4<=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20<G4<=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20<G4<$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4>=$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20))))))))))
Is there a simpler way to achieve the right answer i.e. 751.52 as I
have checked all the cell values and they all read as indicated here.
Where am I making a mistake?
Sorry if this appears critical, but there are a number of things to say here.
o To get the best help from others, you really do need to explain
your problem in detail - and that means words, not formulae or
values. Once you have defined your problem, it is very likely
someone can help you. Indeed, explaining your problem concisely and
completely may well help you to see how to solve it yourself!
o You have laid your values out as a row (in row 20) - as if it's a
vector, a twelve by one array. But surely it is a four by three
array? Setting this out as four rows of three columns would help you
see what the likely solution was. And you have not used those
products (H20, K20, etc.) in your formula: instead, you have
effectively recalculated them all, so they are just distracting.
o I always hope it's obvious (but it's clearly not) that you cannot
define what you are trying to calculate by quoting a formula that you
know doesn't work. This must indicate what you *don't*
want! Please, please omit all the detail and say what you do
want. (Forcing yourself to do this is a good first step in solving
any problem.)
o Any formula as complicated and repetitive as this cannot be the
right way to do things. Apart from anything else, you might not
notice any slip you might have made in the middle of the formula, and
it would be quite easy to miss it during testing. Anything
repetitive should be reduced to a simpler formula that nevertheless
deals with all situations.
o You have apparent logical expressions such as 0<G4<=$G$20. Whilst
A<B<C is meaningful in mathematics, where in the documentation did
you find any suggestion that it makes sense in spreadsheet
lingo?! Since it's undefined, I'm having to guess what this will
mean to Calc. First, 0<G4 will be evaluated, giving the result TRUE
or FALSE. Now the rest of the expression reduces to TRUE<=$G$20 or
FALSE<=$G$20 - which make no sense, of course. In practice, it seems
that the logical values are interpreted as numbers - with FALSE being
zero and TRUE being one - and this numerical value is then compared
with $G$20. But these values are 150, 200, and so on, which will
always be greater than either zero or one, so all the conditions will
evaluate to TRUE!
o Your last IF function has a argument for truth but not one for
falsehood, so the logical value FALSE will be substituted. You have
given no alternative because your final condition is bound to be true
if the previous ones have all been false, so you don't need
it. Remove that IF!
o Remember that multiplication precedes addition and subtraction in
evaluation, so many of your parenthesis pairs are unnecessary. You
may think they clarify things, but in my experience they obfuscate
the meaning of a formula and mask errors.
There is a lot here that anyone helping is still having to guess, but
the problem is almost certainly best solved using VLOOKUP(). I
haven't had time to look at it yet ...
I trust this helps (so far).
Brian Barker
--
To unsubscribe e-mail to: users+unsubscribe@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
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: 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.