Hi
We want to set a selling price based on the cost price of the produce and the quantity ordered.
I have 4 formulae based on this but it is clearly untidy and I'm sure there's a better way to do this. It would be great to have it as one formula looking at two cell references, one quantity the other cost price.
Any suggestions would be gratefully received.
Please Login or Register to view this content.
Last edited by Steven811; 07-11-2014 at 08:08 AM.
Why not use a matrix? Then you can just index/match the matrix for qty & cost price and get the factor out?
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
Hi Nathansav
The formula is to be used by our web developer and I want to keep it simple and as idiot proof as possible. I can use a pricing matrix and the Lookup function but thought that a single formula could do the job, I just don't know how to do it as you can see.
For future reference, the formulas could have been simplified as follows (Qty 1 to 4, for example):Originally Posted by Steven811
I have 4 formulae based on this but it is clearly untidy and I'm sure there's a better way to do this. It would be great to have it as one formula [...].
Please Login or Register to view this content.
=CostPrice*IF(CostPrice<=4.99,5,IF(CostPrice<9.99,3.75,3))
But for the combined formula, try:
You can eliminate the IF(Qty<1,...) wrapper if Qty is never less than 1 (i.e. never empty).Please Login or Register to view this content.
What about this? (formula in A5 thru A16)
Thanks, that sorts the problem
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks