Running Multiple calculations per record per table across 2 tables

Hi Paul and welcome to the forum.

I'm pretty sure I know you from another board where you have helped me more than once.

It would probably be better if you could upload a workbook here.

You can click on go advanced and use the paperclip icon.
ViewPic

Paul,

I think this may be possible but I'm no expert on formulas. What do you mean: "the calculations are just pmt etc. etc."?

In any case, I can propose a very simple solution that can be carried out with simple linear methods below. I described it exhaustively, but I want to make sure I understand the problem and how to simplify it moving forward: Y = P*Q*X. More on that later. As a worst-case scenario, what I understand your problem to be can be solved by hiding a few intermediate calculations somewhere in the workbook, but I expect it can be done in one line with a clever use of Array-entered formulas or something like that (never worked with "A.-e.'d formulas" before so I could be wrong about the one-line thing).

Working at this piece-wise, I can build expressions of individual conditions such as (note: the following formulas are written in the Inventory Worksheet for testing purposes):

Qualitative description of Matrix Q (described below) for one row/column, depending on how we define our indicies:
{
Category_________||Specific Example (which can be generalized)_____________________________||=Formula
Max Miles Condition||E2 must be less than Rates mileage condition_____________________________||=Rates!G2>E2
Min Year Condition || Extracted Year from B2 must be equal to or greater than year specified by Rates||=Rates!C2<=INT(LEFT(B2,4))
.
.
.
Category W_______||Specific example W -- for clarification purposes___________________________||=FormulaW
}
...and then look at several different rows in the rates.

I can explicitly find the conditions for all "W" conditions.

What are your thoughts of building the individual expressions and then testing the entire inventory for each of the regions? That would involve an intermediate operator (matrix) of dimension NXM of Boolean values based on W conditions, where (again) W is the number of conditions, N is the number of cars in the inventory, and M is the number of bank regions. With an AND(Condition1, Condition2, ... , ConditionW) statement, you can determine whether or not you perform a calculation in order to find the optimal bank/region.

With Y = P*Q*X, Y is a 1xN vector of the best banks (your results/output), X is an 1xN vector of inventory (input), P is an NxM matrix (the math you do to figure out which bank region gives you an the best rate, without considering logical conditions) -- don't worry about the fact it's a matrix instead of formulas... that will become clear later, and Q is an MxN matrix of logical conditions as described above (without considering the best rate).

Does that sound like the direction in which you'd like to proceed or am I missing something?

Joe

...Assuming that ADV is located in cell: AB2 in the Rates Worksheet, your formula, as I understand it:

Cost - ((((Cost * Adv) - TitleFee)/(1+Tax)) - DocFee) is =Inventory!V2-((Inventory!V2*Rates!AB2-16.5)/(1+0.0325)-899)

Let me know if adjustments to the formula above are required.

I understand you want to generalize this, but before I can help with that, I need to understand ADV. If there was one column of ADV values or a 1:1 correspondence between Inventory and ADV values, it would be simple but I don't see any such apparent relationship (but I'm sure there is). Perhaps is there just one ADV value omitted on the rates worksheet and there should be 6 instead of 5 b/c there are 6 vehicles in the inventory?

Now... would you like to work on determining the best bank for the inventory you have with a simple Y = AX-style solution? That way, a user can put in any number of vehicles in the inventory, any number of banks in the inventory, and an output will display the best bank to use per vehicle without thinking about it.

First, let me answer your immediate question once I know: "Which ADV?" I see 1 ADV, 2 ADV, 3ADV, ..., 5ADV.

As far as the formulas, don't worry about it. If we work on one simple case, we can generalize the solution if we simply hide a few values in the spreadsheet somewhere. It really is as simple as Y = A*X, with a clever choice of A!

I'll even venture to say that anything can be described by Y = A*X, with a clever choice of A.

Paul,

Ok. Sounds like you've got some work to do before we move forward. I'll send you a private message with my e-mail because this one links up to one I seldom check unless I happen to be on here. Feel free to message me on here or my e-mail and I'll see if I am available/able to help.

Joe

PS I don't recommend confusing yourself with thinking about matricies but I'll break it down for a simple [2x1 element] = [2x1 element]*[1x1 element] case

Let's say we have 1 car that costs $20,000
We have to pay two taxes: State tax(3%) and Federal taxes(7%) on the purchases in the USA but...
We have to pay two taxes: Local Tax(1%) and Government taxes(8%) on the purchase in Mexico...
Let Y = A*X, where Y is taxes paid, X is price of the car, and A is a matrix of tax rates -- let's say in the USA.

Now you can easily figure out w/o thinking about matrices that you pay in taxes: $20,000*.07+$20,000*.03 in the US and a similar expression for Mexico.

All a matrix does is simplify and generalize things. so you can write: Y = A*X instead of $20,000*.07+$20,000*.03. You can also think of it like a table -- that's all a matrix really is. But if you never worried about matrices before, I wouldn't start now. All I need to know is the $20,000*.07+$20,000*.03 part of the problem.

just to clarify on ADV and Rates, the table is laid out so that each particular offer/package are going down the rows, right at the end of this portion of the table there is 2 more sections (Rate and ADV), and just below these there are a set of ranges (), these i think are like credit ratings and depending on this rating depends on which Rate and ADV applies, like i mentioned in the last post i will rearrange this table slightly and duplicate the rows to allow these ranges, Rating and ADV to each have their own row so the lookup is straight forward........Hope this explains it a little better.


Originally this rating/credit rating value was going to be a variable that the user chooses but its now been decided to work out all of these rating, this is going to increase the amount of calculation alot but will give the data needed to target and match buyers, to pbank plans with certain gains or losses (not sure about the internal thinking behind this, all i know is they need a couple of variations with the calculations) and they at least want to see the best option, and if possible the whole list of options and their ranks