You don't need the double If to start. Use:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k pricing
sheet'!I7,IF(E8=299999,'=200k pricing sheet'!I7,IF(E8=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7)))))
Also, you need to test for <= rather than =, because everything greater
than 199999 will be caught by the second test, and nothing between 50000 and
199999 will be found. Use:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8<=199999,'=100k pricing
sheet'!I7,IF(E8<=299999,'=200k pricing sheet'!I7,IF(E8<=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7)))))
Next, I would use < rather than <= in case you have a value like 199999.50:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8<200000,'=100k pricing
sheet'!I7,IF(E8<300000,'=200k pricing sheet'!I7,IF(E8<500000,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7)))))
Next, you don't need the last If statement:
=IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8<200000,'=100k pricing
sheet'!I7,IF(E8<300000,'=200k pricing sheet'!I7,IF(E8<500000,'=300k
pricing sheet'!I7,'=500k pricing sheet'!AB7))))
Finally, I would put all your prices on one sheet, and use a Vlookup
instead. It's much easier to maintain.
Regards,
Fred
"Julie HSV" <Julie
wrote in message
...
I have a worksheet with average unit prices for <=50k,
<=100k,<=200k,<=300k,<500k and am creating a cover worksheet which will
pull
the relevant unit cost based on the volume entered.
EG, I have a volume of 150k so I want the formula to take the price from
the
<=100k sheet. I originally did the formula for sum(if xxx) but it added
each unit price instead of looking for the correct volume price.
Tried this but have an error - anyone able to help me ?
=if(IF(E8<=50000,'=50k pricing sheet'!AB7,IF(E8=199999,'=100k pricing
sheet'!I7,IF(E8=299999,'=200k pricing sheet'!I7,IF(E8=499999,'=300k
pricing sheet'!I7,IF(E8=500000,'=500k pricing sheet'!AB7))))))