Mar 12 2019 08:23 PM
I have created a table (only on paper so far) in which Column A contains 6 Revenue Ranges. Row 1 contains 5 Machine Quantity Ranges. The "Data" in the table is simply the number of preventive maintenance visits I have established based on the defined ranges. For example, a revenue range of $4001 - $8k and a machine quantity of 1 is 2 visits. There is no formula in place to create the data...I just entered the values manually. Easy.
I have a master spreadsheet showing all of my active accounts. Column M shows Annual Average Revenue per Machine and is labeled AVG/MACH. Column K shows the total number of machines at a particular location and is labeled TOTAL UNITS. In Column P, I would like to enter a formula that compares the values in Columns M and K to the corresponding ranges in the table and returns the corresponding number (the number of pre-established visits I defined in the table).
This seems like it would be relatively easy to do, but I have no idea where to start. Please Help!!!
Mar 12 2019 09:44 PM
Mar 12 2019 10:09 PM
I didn't have a sample Excel Workbook when I first posted...I do now.
Mar 12 2019 10:55 PM
The row and column values in sheet "Draft Frequency Table" need to be numbers.
See attached file.
Mar 12 2019 10:56 PM
Hello Erik,
In the attached file, the formula in P2 is:
=INDEX(VisitTable,
MATCH(M2,RevenueRange,1),
MATCH(K2,MachineQtyRange,1))
Note that I modified the Row and Column Labels to suit the formula for an approximate match.
Cheers!
Twifoo