thanks for reading my question.
I'm trying to find price points for various sizes of product packages. I have a data range on Sheet1, A1:Z102, with the top row as mass of the packages (B1:Z1), and the first column (A2:A102) as the selling price. In the array I've calculated the profit margins (B2:Z102)
I'm trying to set up "what-if" automatically on a sheet that allows me to enter various weights "Sheet2!B1:B10" -- that will select the corresponding column of package weights from "Sheet1!A2:Z2", then have the function MATCH the closest row of that column to my "what-if" profit margin "Sheet2!A1", and finally return on Sheet2, C1:C10, the selling price that is closest to that profit margin.
SHEET1 1lbs 2lbs 3lbs 4lbs $1 $0.10 -$0.80 -$1.80% -$2.80 $2 $1.74 $0.74 -$0.26 -$1.26 $3 $2.61 $1.61 $0.61 -$0.39 $4 ... ... ... $0.47 ... SHEET2 $0.20 2lbs X 4lbs X
[Edit1] So the function would return C1=$2, and C2=$4 in three iterative steps (I think).
- Cell.Sheet2!B1.Value matched to identical column in Sheet1 Row1: Returns Column Y
- For Column Y in Sheet1, find value closest positive value to Cell.Sheet2A1: Returns Column Y, Row Z
- For Sheet1 Column Y, Row Z return value in Sheet1 Column 1 Row Z. repeat that value in C1 Sheet2
- Repeat 1-3 for B2...Bn
I'm having a hard time solving each step:
- MATCH can identify correct column
- Can't figure out how to nest MATCH in "OFFSET" to search only Column Y. I've tried INDEX MATCH, INDEX MATCH MATCH, LOOKUPs(V&H).
- Return Column1 value in RowZ.
I'm realizing this might be a Macro solution, but I just don't know how to VBA it:
Dim profit, lbs, cost, reflbs, refprofit As Range Set cost = Worksheets("Sheet1").Range("$A$2", "$A$5") Set profit = Worksheets("Sheet1").Range("$B$2", "$E$5") Set lbs = Worksheets("Sheet1").Range("$B$1", "$E$1") Set reflbs = Worksheets("Sheet2").Range("$B1") Set refprofit = Worksheets("zTest").Range("$H$39") For Each profitCell In profit For Each costCell In cost For Each lbsCell In lbs If lbsCell.Value = reflbsCell.Value Then
Then I'm not sure how to direct a search in the column whose header lbs matches "reflbs", and then I'd put in a:
If profitCell.Value = refprofitCell.Value Then
Then return the Row Z for the match, then ultimately return Row Z, Column A Sheet1! in Sheet2!C1.
Thanks a bunch!