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: [email protected]
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
