I'm currently trying to build a spreadsheet for my consignment vendors in order to keep track of their items in a database format. I am stumped at one particular formula for the total.
Many items have more than one quantity (same item category, description, and price) which I want in the same row (to avoid the same information having to be entered multiple times).
Some of these items may or may not be sold at a discounted price.
I want to take the price of the item and multiply it by the quantity sold and subtract the discount (percentage) for the quantity sold at the discount and not the items sold at full price.
for example: I have 5 books being sold for $10 each (H3), 3 were total have been sold (I3), two of them (K3) were sold at 50% off (J3) and one was sold at full price (reverting back to I3 as only 2 were sold at a discounted price)...the total should be $20.
If the qty of the discounted items changed to 3 the total should change to $15.
I can't for the life of me figure out how the math should work in order to get the correct formula... any help would greatly be appreciated.
Many items have more than one quantity (same item category, description, and price) which I want in the same row (to avoid the same information having to be entered multiple times).
Some of these items may or may not be sold at a discounted price.
I want to take the price of the item and multiply it by the quantity sold and subtract the discount (percentage) for the quantity sold at the discount and not the items sold at full price.
for example: I have 5 books being sold for $10 each (H3), 3 were total have been sold (I3), two of them (K3) were sold at 50% off (J3) and one was sold at full price (reverting back to I3 as only 2 were sold at a discounted price)...the total should be $20.
If the qty of the discounted items changed to 3 the total should change to $15.
I can't for the life of me figure out how the math should work in order to get the correct formula... any help would greatly be appreciated.
