Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


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.