Hi Everyone! Apologies if this post is really long, I'm not great at keeping my thinking concise.

TL:DR - Want to calculate sales prices per different product based on total sales price given to me and the total cost of the "deal". Ideally want to do this by staying as close to the sales-cost ratio as possible. Rounding each product to 2 decimal places and re-calculating causes the new calculated total sales price to not match existing total sales price. Looking for ways to fix this/find an appropriate solution, and/or provide alternative value if no possible solution exists. Ideally to be completed in Microsoft Excel or alternative suggestions would also be acceptable.

https://preview.redd.it/tu46agk4t9cd1.png?width=1564&format=png&auto=webp&s=11f17b8d1aa66d236fa39590efde61e0ed7018c2

Essentially this is me phrasing a simple table very complicated:

In an example below, I know the following:

I have 6 (n) products, and I know the quantities and costs for each that are required.
I also know the total Sales Price is $150.00.

I want to find values for all of S1 to S6 which are 2 decimal places, and make sense.
(i.e. relate as close as possible to the ratio between total cost and total sales price.
I've calculated a Sales to Cost Ratio by doing Total Sales / Total Cost = $150.00/$53.82 = 2.787068....
I can generate the table below.

I can calculate the Total Sales Price per unit by multiplying each Total Cost by the Sales to Cost Ratio.
I can then get the Unit Sales Price by dividing the Total Sales by Unit Quantity.

This gives me S1 = $4.180602... S2 = $2.75919... $S3 = $2.78706.... etc...

The Problem here is that the Unit Sales Price needs to be 2 decimal places precisely; because it is used in other software which only accepts 2 decimal places.
Now, if I round these unit prices to 2.d.p, then multiply by the Unit Quantity and sum together, I wouldn't get $150.00 because of the rounding I did here
In this example, it gives me $149.99 which naturally is really close, but isn't the exact figure and it NEEDS to match precisely.

So I'm looking for a way to calculate these sales prices so that when you multiply each by their respective quantity and sum them together, it matches the Total Sales Price provided for.

I also know that in some situations, a solution wouldn't exist, in which case, I'd want to somehow be able to calculate the closest Total Sales Price that work work to the original Sales Price (or better yet, the nearest highest and lowest).

How I'm trying to solve the problem in Excel

My current thinking is to use the sales-cost ratio to calculate the total sell price per product, round it to the nearest 2 decimal places individually by quantity, then use that value and $0.01 either side in combinations to see if there's a match to the total sell price.
In Excel, I'm doing this by using an array to calculate the combinations, then use that array vs the next array to calculate those combinations and so on... I've set this up for 8 potential products, but this seems doesn't seem like an optimal solution as I'd have to create new formulas if there were more than 8 products, and it might not even return the correct solution if it's more than $0.01 out for it to work. Plus I'm still unsure how to return a "potential" alternative sell price if there is no solution.

Here's the link to what my thinking is so far: https://easyupload.io/ynqzvb