See attached. Using "helper columns" makes it trivial. However the spreadsheet design is poor and is likely to lead to errors in use because you have the opportunity to insert conflicting data.
It is axiomatic that in any database design any item of data is inserted and stored
once and once only. Thus you would be much better to have columns headed Quoted, No sale, Ordered, In progress, Delivered etc, and put a 1 in each column as the order situation changes.
I did the Euro conversion manually but it could presumably be done by an IF statement which checks the currency format of the cells in col A though I don't know how. Search the forum with
currency for ideas.
I manually typed the " 1 " and " 0 " in cols D and E but they could easily be calculated by an IF statement together with an OR statement: IF (C4="In progress"OR"Delivered" ...) etc. However be aware that if
you type "In progress" in col C you will inevitably misspell it sometimes, put in two spaces, add an extra space at the end, or type "In Progress", and these will not be recognised. It is far better to use codes such as P (Progress) D (Delivered) etc as they are visually correct. Or type the " 1 " in the respective column. The " 0 " in cols D and E is not required.
Or have a
Status column where you place a D, P etc. You now cannot add conflicting data ... though you can add the wrong character and cause the calculation to be wrong.
The formula in F4 is as below
But will give the wrong result if you put a " 1 " in
both col D and col E.
The general advice for business users is "
Never, repeat never, write business software. Always buy a package where all these Gotcha!s, and countless more, have been thought of and taken care of". For example, how are you handling the varying £-Euro rate? The same for all orders ignoring the changes? The value at order acceptance? The value at payment when you receive the Euros?
Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your
first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.