Using MATCH and INDEX function with multiple criteria

Given your use of XL2007 you can use SUMIFS, eg:

Please Login or Register  to view this content.

For a pre XL2007 (and non-array) version you could use LOOKUP, eg:

Please Login or Register  to view this content.

Both assume only 1 rate will be applicable for any given combination of the variables specified in B2:B4, if not the former will aggregate and the latter will return the last price found.

In truth that will be more complex given the tiered nature of the calculation.

Is it your intention to calculate multiple "prices" simultaneously (ie 1+ set of variables at any given time) - if not you could simply calculate adjacent to your "rates" table and sum output (pretty straightforward).

Presumably all dates will be Mondays - no part weeks etc...


On a final note - the SUMIFS earlier is not an Array and as such can be entered with Enter as normal.
(SUMIFS is function added to XL2007 and beyond - alongside COUNTIFS, AVERAGEIF, AVERAGEIFS, IFERROR etc...)

I understand the requirement in terms of a singel cell calculation of a tiered price but what I'm getting at is whether you'll be conducting multiple of these calculations simultaneously, eg:

MTSLI, General English, Start date 01/03/2010, 24 weeks

and

MTSLI, General English, Start date 01/03/2010, 12 weeks

If you are then you need to look for a relatively elegant solution, if not you can run basic calcs adjacent to your table of rates and simply sum results


Incidentally, shouldn't the output for the first be 4120 rather than 5740 ?
ie 5 weeks @ 140 and remaining 19 weeks @ 180

If not, can you outline the math as I'm misinterpreting something I think.

Just as a starter... and assuming 4120 was correct...

If we assume you want to calc in a single cell (and not run calcs adjacent to rates) then first you would be best served introducing a further column to your table which holds the "incremental" changes between rates.

Using your earlier file as the basis:

Please Login or Register  to view this content.

The above should generate three values which reflect the movement in value between current & prior rate: 140, 40, -60

To avoid repetitive calcs I would then be inclined to calculate the End Date in a further cell, eg:

Please Login or Register  to view this content.

Note this value will be the Monday following the last day of the course - this is fine - don't change!

We can now calculate the total cost of the course using the following Array:

Please Login or Register  to view this content.

So as you adjust the values in B2:B5 the total cost should adjust accordingly.


In reality I suspect the "incremental" rate formula will need to be adjusted to account for changes in School / Program - ie change in either from prior result is equivalent to brand new rate and thus prior rate should be ignored.

Sorting data nearly always allows for greater efficiencies...

If the data is unsorted and you prefer to not use the incremental column then another single cell calculation would be:

Please Login or Register  to view this content.

As before the above utilises new formula in B6 (outlined in prior post)

As previously mentioned (a couple of times) B6 should contain the following:

=B4+(B5*7)

If you do that you will get your result.

The ranges used in my formula (ie references to rows 9:11) were based on your earlier file.

Simply change the ranges used in the formula to reflect your real data.
Using your latest file that would mean changing all references to row 11 to row 16 - remember to reset the Array once you've modified

If the dimensions change all the time you may wish to consider using Dynamic Range(s), if so I'd be inclined to steer clear of using OFFSET in it's creation use INDEX instead.

I think you're misinterpreting my post(s)...

To be clear...

The formula you've been provided with expects to review the rate table in it's entirety... ie all rows should be referenced.
The conditional tests within the formula will omit those rates that do not apply (either based on date, school, program)


If the rate table is fixed in size (ie you're not constantly adding / removing rows) simply reference the entire table in the formula provided (add a few excess rows if needed)

Personally speaking I would only use Dynamic Named Ranges if you were always adding / removing records such that the number of rows would increase dramatically in a short space of time - I am guessing you don't.

I'm afraid I'm not sure I follow.

If the possible weeks are themselves determined by the selection in B2 why not simply modify the DV list in B5 on that basis so as to prevent incompatible selection in the first instance ?

I don't really see any value in adding yet more complexity to an already complex formula when it can be (seemingly) avoided.

For ex. if you assume you list the MIN/MAX against each combination in your Prices sheet then it follows you could use:

Please Login or Register  to view this content.

to determine Week start & end...
(if the naming convention of each course was 100% consistent you could even calculate from the string itself in B2)

If for sake of demo we assume the above are stored say in cells C2 & D2 on Calc tab then you can revise the DV list source for B5 to be:

Please Login or Register  to view this content.

at this point only the valid weeks will be available for selection

(the above is not ideal but based on setup it might be the quickest to implement)


NOTE: you would need to add weeks 37 to 52 to your WEEK range ... ie up to and incl. the MAX of Prices!E:E

Originally Posted by Babylon

One main data quality hurdle remains: is it possible to have a dynamic dropdown list that looks for the minimum and maximum value for number of weeks for a given course?

see Post 17 for basic concept ... given the multi conditional nature of the lookup it makes sense to make use of AVERAGEIFS to return MIN & MAX.

I'm glad you've resolved... I'd literally just begun looking at this again prior to your post so I shan't worry now if you're happy that it's doing what it should be doing.


EDIT: disregard point re: post 17 ... it seems things have changed in so far as one given course could have multiple week durations.

The following would return MIN & MAX week number for given combination of School, Course & Date

Please Login or Register  to view this content.

You can then adjust your Validation Source in B6 to:

Please Login or Register  to view this content.

Re: ignore... just to clarify

AVERAGEIFS won't work now given that a given school/course/date combination can have more than one unique MIN value meaning the Average would be misleading (same holds true for MAX obviously)
(before the MIN & MAX values were constant as I recall as I believe the weeks were included in the Course name (to an extent - eg 16+))

In my prior post I failed to use Spanish delimiters - so the commas should all become semi colons - I will edit now.

Again, I'm not quite sure I follow I'm afraid... can you elaborate regards:

Originally Posted by Babylon

...to present this in an email to a client, we'd like to have the items listed in the tab "Variables" translated in Spanish (and perhaps later in other languages)

what is the content of the email exactly ? The model in full ? Or are you issuing only the form element (ie Calc tab) as some sort of quote which you want translated as appropriate ?

I confess I'm still not sure if your intention is to mail just the results or have the calculator itself work in multiple languages ?

Regardless... you could "in theory" cater for both by doing something like:

a) creating a sheet - let's call it TCourses which stores

1. the course names (only) in English in Col A
2. their equiv. translations in Cols B onwards (one column per language)
3. the language "name" at the head of each column in Row 1 (English, Spanish etc..)

b) on your Calc tab add a further validation list at the head of the page which is source from row1 on TCourses sheet

c) modify the courses validation list so that now it references the appropriate Column from the TCourses sheet for it's source based on the language chosen in the above DV list

d) add a cell (hidden if pref.) which translates the (potentially) non English course name to it's English equivalent...
this again can be calculated based on the known language and the fact that the English courses are always Col A in TCourses sheet

e) base all calcs on the English translation cell(s) so that you can leave the Prices matrix as it is (ie in English)


I say translation cells (plural) because it follows that you would need to repeat the Courses process for other fields also (School (poss.), Accommodation, Airport)


If you could create the translation sheets (TCourses, TAccommodation etc...) and post back I will happily help you revise the Calc tab accordingly.

OK leave this with me... I might make some alterations regards how some of the dependent validation lists are set up ... not in terms of the final output of course just in terms of config.

In hindsight... rather than get too involved restructuring I figured for expediency we could leave the sheets as they were and work on the Calcs tab.

In the attached version I've made a few changes...

Col F onwards need not be visible - these are the cells that will do the translation work.

The DV lists in B5, B7 & B9 have been modified such that they now source from the translation listings on the Calc tab.

The translations themselves will update per the various "T" sheets in line with the chosen Language.

I've added some Error based approaches that basically mean if any choices are not valid (ie legacy selections based on prior language) then

a) the Calcs section won't calc

b) the erroneous cells will be highlighted accordingly for ease of review / correction

On the "T" Airport / Menu sheets some values / translations were missing - I've simply added in as place holders (eg ?) for the time being - they are in red.


Adding further languages should be as trivial as updating the various "T" sheets wiht the translations - everything thereafter will take care of itself.
(obviously you should use the same columns on each "T" sheet for the same languages)

I've demo'd this by simply adding Col C to each of the "T" sheets... the Calcs table will simply reflect the additional language in B1 dropdown and upon selection the remaining calcs will resolve themselves per their English equivalents.


Once you're happy with what you have you should think about adding some basic sheet level protection to the formula cells so they can not be overridden by accident.

Regards your penultimate post see if the attached does what you want... (pre-requisite flags added)

EDIT: attachment reloaded at 10:26 AM UK time (Conditional Format correction)

Not entirely sure I'm still following but perhaps the attached does what you want (?)

EDIT: file removed at 12:25 PM UK Time in respect of latter post

The last attachment was still wrong - the LOOKUP won't suffice - Arrays are required.

See attached.

Perhaps amending per below

Please Login or Register  to view this content.

You will need to add yet another test to the Array to differentiate the courses based on Level chosen (M3) and the Min/Max levels associated with each transaction.

Truth be told this model is pretty much "out of control" - you need to be able to maintain this going forward.... the more ifs & buts you put into it the less manageable (and inefficient) it will become.

I was going to modify my last post but you've since replied so edit below:

Initially I was loathe to suggest redesigning what you had as given the initial requirements it wasn't really warranted....

However, n feature creeps later I would suggest that as and when (and if) you get to a stage at which your feel no further logic additions are required then it is my belief that at that point you should seriously consider how to be best rebuild those requirements into a more practical model.

Regards the last tweak, as mentioned - add the Level test into the Array - this is not really any different to your existing Week MIN/MAX test which is already being applied.

I'm glad you added it yourself - I was politely prodding as it's important you can tweak it (and feel comfortable doing so).

Regards redesign - that's most definitely one for "manana".

To reiterate though, it's really only worthwhile considering once you have established all requirements.... it only takes one further tweak to throw a spanner in the works of whatever re-design you had in mind.

sorry...posted in wrong place

raja63,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

I have already asked you to post your question in a new thread, not here.