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

As written you have one too many IFs and one extra) at the end

This will do the lookup.
=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)))))

If you were trying to add another condition (like if e8=0 return 0) you need
to add it like this
=IF(e8=0,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))))),0)
--
If this helps, please remember to click yes.


"Julie HSV" wrote:

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

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