New Contributor

# First Post - Formula help greatly needed

Hello Peeps,

Hope you guys are well, I'm very very new when it comes to Excel, so for some this will be the most basic thing. So by profession I am a doctor, but before Med school I used to sell little stuff on eBay, and Amazon this is something I still enjoy doing despite the lack of real profits.

To really make the help required easily comprehendible I have made a template file of all the stuff in simple format. Would love to hear from you guys how to improve this.

The three formulas I am struggling with are explained below.

Q1 - I have three suppliers for identical products, now within these suppliers the price varies as its subject to the USD rate. So I need in column E, G & I the most recent price based on invoices. I don't know how to write this formula but understand that this will be based on a condition of date and supplier.

 Date: 16/03/2023 Date: 14/03/2023 Date: 08/03/2023 Date: 01/03/2023 Date: 12/02/2023 Date: 10/02/2023 Date: 01/02/2023 Invoice #: 5001 Invoice #: A212 Invoice #: A208 Invoice #: XYZ721 Invoice #: 4985 Invoice #: 4722 Invoice #: XYZ718 Supplier A Supplier B Price Supplier C Price Supplier C Supplier B Supplier B Supplier A Supplier C Supplier C Supplier A Item Title SKU Price Supplier A SKU A Price Supplier B SKU B Price Supplier C SKU C Price SKU QTY Price SKU QTY Price SKU QTY Price SKU QTY Price SKU QTY Price SKU QTY Price SKU QTY Price Banana FB 0 A101 FB54 X0A056 X0A056 4 £2.00 FB54 2 £2.00 FB54 6 £2.00 FB54 6 £1.00 X0A056 4 £2.00 X0A056 0 £1.95 FB54 6 £1.20 Apple FA 0 A102 FB55 X0A057 X0A057 4 £4.00 FB55 3 £4.00 FB55 6 £3.50 FB55 2 £1.00 X0A057 4 £4.00 X0A057 0 £3.85 FB55 6 £1.00 Orange FO 0 A103 FB56 X0A058 X0A058 4 £3.50 FB56 0 £3.50 FB56 6 £3.00 FB56 6 £1.00 X0A058 1 £3.50 X0A058 2 £3.40 FB56 6 £0.99

Q2 - In sheet named 'Reorder Sheet' I need to use data from sheet 'Restock Data' to generate reordering quantity. So this is a COUNT formula but because first three items are single units and the rest are bundles of the said three different units (see sheet name 'listings'), I am unsure of how to make it count from the bundles.

 Item Title SKU Units Required Supplier A SKU A QTY Supplier B SKU B QTY Supplier C SKU C QTY Banana FB A101 0 FB54 0 X0A056 0 Apple FA A102 0 FB55 0 X0A057 0 Orange FO A103 0 FB56 0 X0A058 0

 B0001 33 B0002 22 B0003 15 B0005 100 B0007 23

 Item Title ASIN SKU Number of Units Price Unit 1 SKU Unit 1 Price Unit 2 SKU Unit 2 Price Unit 3 SKU Unit 3 Price Banana B0001 FB 1 £5.00 FB £0.00 Apple B0002 FA 1 £5.00 FA £0.00 Orange B0003 FO 1 £5.00 FO £0.00 Bundle 1 B0004 SET-BA 2 £9.00 FB £0.00 FA £0.00 Bundle 2 B0005 SET-BO 2 £8.50 FB £0.00 FO £0.00 Bundle 3 B0006 SET-AO 2 £9.00 FA £0.00 FO £0.00 Bundle 4 B0007 SET-BAO 3 £12.00 FB £0.00 FA £0.00 FO £0.00

Q3 - This I don't know anything to be honest. I hope I can explain properly. So basically I need the values in cost of goods column in Sales - May 23 to be based on the invoices, so once a quantity is used up from the oldest invoice (in catalogue sheet) I need the formula to jump to the next invoice and use the respective price. I gave up on this and just thought using an average will suffice but that makes it more confusing.

 ORDER # ASIN QTY Price Per Unit Total Price SHIPPING COST OF GOOD PACKAGING Total Costs PROFIT TAX 788782 B0006 1 £10.01 £10.01 £2.50 £0.20 £2.50 £7.51 £1.86 788783 B0003 1 £5.22 £5.22 £2.00 £0.20 £2.00 £3.22 £0.97 788784 B0004 1 £9.85 £9.85 £2.50 £0.20 £2.50 £7.35 £1.83 788785 B0005 1 £11.00 £11.00 £2.50 £0.20 £2.50 £8.50 £2.05 788786 B0005 1 £11.02 £11.02 £2.50 £0.20 £2.50 £8.52 £2.05 788787 B0006 1 £9.89 £9.89 £2.50 £0.20 £2.50 £7.39 £1.84 788788 B0007 1 £11.95 £11.95 £3.00 £0.25 £3.00 £8.95 £2.22 788789 B0001 1 £4.85 £4.85 £2.00 £0.20 £2.00 £2.85 £0.90 788790 B0007 1 £12.44 £12.44 £3.00 £0.25 £3.00 £9.44 £2.31 788791 B0004 1 £10.20 £10.20 £2.50 £0.20 £2.50 £7.70 £1.90 788792 B0007 1 £13.15 £13.15 £3.00 £0.25 £3.00 £10.15 £2.45 788793 B0007 1 £13.11 £13.11 £3.00 £0.20 £3.00 £10.11 £2.44 788794 B0001 3 £6.07 £18.21 £2.00 £0.20 £2.00 £16.21 £3.39 788795 B0004 2 £10.29 £20.58 £2.50 £0.20 £2.50 £18.08 £3.83 788796 B0007 1 £11.74 £11.74 £3.00 £0.25 £3.00 £8.74 £2.18 788797 B0006 1 £9.85 £9.85 £2.50 £0.20 £2.50 £7.35 £1.83 788798 B0007 1 £11.70 £11.70 £3.00 £0.25 £3.00 £8.70 £2.18 788799 B0004 3 £9.52 £28.56 £2.50 £0.20 £2.50 £26.06 £5.31 788800 B0001 1 £4.99 £4.99 £2.00 £0.20 £2.00 £2.99 £0.93 788801 B0006 1 £11.02 £11.02 £2.50 £0.20 £2.50 £8.52 £2.05 788802 B0007 1 £13.99 £13.99 £3.00 £0.25 £3.00 £10.99 £2.60 788803 B0001 9 £5.00 £45.00 £2.00 £0.20 £2.00 £43.00 £8.37 788804 B0001 2 £5.09 £10.18 £2.00 £0.20 £2.00 £8.18 £1.89 788805 B0001 2 £5.05 £10.10 £2.00 £0.20 £2.00 £8.10 £1.88 788806 B0007 1 £12.28 £12.28 £3.00 £0.25 £3.00 £9.28 £2.28

Any help will be highly appreciated, the file attached is obviously not the real data, this is because I wish to understand how the formulas work in a template so I could recreate it - for me this more of a learning thing. I tried to use Fiverr and the people are on there were prepared to do it for very reasonable prices, but don't want to teach me how the formula is written which I found weird to say the very least.

# Re: First Post - Formula help greatly needed

why can i not attach the full file here?