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
--
========================================================================
Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO
========================================================================
--
To unsubscribe e-mail to: users+unsubscr...@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
