Re: [libreoffice-users] Calc IF problem

Hi.
Brian Barker gave a very succint method to do this in a previous discussion.
You can search this list in Nabble
http://nabble.documentfoundation.org/Users-f1639498.html

Search VLOOKUP
Steve

On 2013-10-11 06:11, Hylton Conacher (ZR1HPC) wrote:
> Hi,
>
> 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?
>
> Regards
> Hylton


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