Help me create a formula

[oybek94]

I am creating a program for a mobile phone store. Sometimes I take back the phone I sold and put it up for sale again.
I want to calculate the price of the phones that are currently on sale. When I count the total amount of phones that are currently being sold, It is calculating taking into account the old purchase amounts of phones that have been sold several times. I need a formula that calculates the total amount of phones on sale. If there is a phone that has been sold several times, it should add the most recent purchase price.
@dbaum 

[WillowMobileSys · Accepted solution]

Instead of trying to accomplish this is some very complicated expression, I would recommend doing the heavy lifting on the row first.

I imagine for each phone in stock, you have a row with a column that holds the "Sale Price".  On this row I would add 2 columns:

1. "Recent Purchase" - a column that stores the price of the most recent sale price of the phone - blank if never sold before.
2. "Price for Total" - a column holds the "Recent Purchase" if NOT blank ELSE hold the "Sale Price"

Now your Total Amount expression is reduced to simply summing up the "Price for Total" column.  The added benefit is that you will have the Recent Sales price alongside the "Sale Price" which will likely come in more handy than you realize right now.