drive.google.com/drive/folders/1mDXkn8NLVrl18ZIiQalwbYUzB9PtIvHh?usp=drive_link

drive.google.com/drive/folders/1mDXkn8NLVrl18ZIiQalwbYUzB9PtIvHh?usp=drive_link

B1 is the cheapest significant item in my inventory, stamped envelopes. B2 is its price, 74¢. A table of its multiples is underneath it. C1 is the next cheapest item, C2 its price. But, for clarity of example, let's consider F1 & F2, Mozzarella 186¢. I want to list its integer multiples (beginning with 1X) underneath it (186, 372, 558...) with each of those prices listed (only) once, on the row with the nearest in value EnvPriceMultiple (which are in B3:B42). So the Mozz 1X (186) is on the row where Env are 222 (Env 3X) [sheet row 5]. And the Mozz 2X (372) is on the row where Env are 370 (Env 5X) [sheet row 7]. And so on. For each col, the item is in row 1, its price is in row 2, and its PriceMultiples are listed underneath that, spaced out, so they align with the nearest in value EnvPriceMultiple. That is, col B is used as a number line, each row showing all items that have a "best fit" PriceMultiple at that price. Of course, we want this automated, where we only have to enter the row 1 & 2 data (items & prices).

My solution was to look at each EnvPriceMultiple (74, 148, 222... or B3:B42) and check if it represents the closest value for any price multiple of the currently considered item. Continuing with Mozz as the item example, I take each EnvPriceMultiple (B3:B42) and mod it by the price of 1 Mozz (186, or absolute reference to the column's row 2; for Mozz, F$2). This effectively takes each row's EnvPriceMultiple and removes any multiples of 186 it contains, leaving a remainder of 0:185.

Whenever that remainder is <= half the price of 1 Env (74/2, or 37), that row's EnvPriceMultiple (B[row]) IS a closest value of a (unspecified) MozzPriceMultiple, since half an Env price is as close as you can be to any EnvPriceMultiple. For example, at B10 (which is Env 8X = 592), 592 mod 186 = 34, essentially subtracting all multiples of 186 it contains (here, 3X or 558, though the actual multiple is irrelevant and lost by the mod), indicating that the 592 EnvPriceMultiple is 34 greater than some (unspecified) MozzPriceMultiple. To determine what that actual MozzPriceMultiple is, simply take the current EnvPriceMultiple and divide by the price of 1 Mozz, then round to the nearest integer. Here being 592/186, rounded; as seen in F10 (i.e., 3, which is 558¢).

We also have to consider (continuing the Mozz example) if the mod result (which, again, is the current EnvPriceMultiple, minus all 186 it contains) is within half an Env price under 186. Such a result (that is, >= 149), just like <= 37, indicates the EnvPriceMultiple is a closest value of a (unspecified) MozzPriceMultiple. For example, at B12 (which is Env 10X=740), 740 mod 186 = 182. Again, the actual multiple is then found by the 740 EnvPriceMultiple divided by 186, rounded. (I.e., 4, as seen in F12.)

To generalize all this: The preferred cheap item's price, or any base numberline value, is put in B2. It is automatically scaled by 1:40, via B3:B42. Each additional column has an item price in its row 2. For B3:B42, and a CurrentCol, B[CurrentRow] is a closest value of some multiple of CurrentCol$2

if ($B[CurrentRow] mod CurrentCol$2) <= $B2$2/2 OR >= (CurrentCol$2 - ($B2$2/2)).

Translated to a formula in C3: =if(or(mod($B3,C$2)<=$B$2/2,mod($B3,C$2)>=C$2-($B$2/2)),concatenate(round($B3/C$2,0)," ",round($B3/C$2,0)*C$2),"")

The value_if_true (the concatenate function) displays the ItemPriceMultiple integer & resulting price. (The 3 formula references are: $B3 is CurrentRow numberline value; C$2 is CurrentCol price for 1 item; $B$2/2 is half the base numberline value.)

I have 2 questions. I'm first wondering if there's a more efficient way to write that formula. That is, to implement my solution, and do so using a single formula in all the cells (and not using macros).

My second question is if there's a more efficient, different solution altogether. But, even here, one that could be implemented with cell formula, rather than macros. (I'll just close with the note that the sole purpose of the spreadsheet is for the printout of it, in case that's relevant.)

Thanks to everyone who got this far.