Predetermined Sliding Scale

I am trying to calculate user costs for a number of projects. These projects will pay for their users on a sliding scale. As the number of users goes up, the unit cost per users, goes down.

I have a chart of costs that is based on two factors. The number of users and which application they are using.
I have blocks of users from 0-10,11-20,21-30,31-40 ..... The Second factor is which application they are using. For simplicity they are using APPs A or B, OR A+B. There is be a different cost based on which application and how many using it.
an ASCII chart has been provided

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I would like some help in creating a formula for user cost that will factor in which app they use and how may users on the system. Can someone help point me in the right direction?

I have an intermediate understanding of formulas and functions in excel.

Thank You!

Sample files often help here so people can see where the data is to located etc...

Assuming (as implied) the calculation is not marginal then in reality it's basic INDEX/MATCH (or even SUMIF with INDEX) - attached is a basic ex.

I've added a tiered/marginal approach also just in case...

edit: ignore the marginal - won't work accurately with the same bands as used for the non-marginal approach (would be 0,10,20,30)

Ok, so INDEX and MATCH functions are what I need to look-into
Sample uploaded to original post.

Yes ... your values in Col A should be the value of the lower boundary (only) eg:

Please Login or Register  to view this content.

Then it's just a case of matching as appropriate, eg:

Please Login or Register  to view this content.

Note: your sample value for G18 is wrong I think - should be using D4 rather than D3 as multiple

Yes ... your values in Col A should be the value of the lower boundary (only) eg:

So in my range of users, only the bottom of a range, say 0-9, only use the 0, That makes sense, it will check if value is between it and the next in range.

I just printed out your formulas to look at it on paper.
I am using your formula, now we shall see.

Thank you so so much for your quick and useful replies

So I took your formula, and augmented it to fit my spread sheet.

=$F17*INDEX($B$3:$D$11,MATCH($F17,$A$3:$A$11),MATCH($B17,$B$1:$D$1,0))
F17 is # of users to calculate against.
I get that INDEX is table of Costs
1st MATCH # of users in calculation, then range of available user blocks(represented by the bottom of the user range)
2nd Match is matching the project APP type against the user price headings

right?

But it seems to result in" #NA "in the cell

The first match uses the default match_type of 1

The second match uses a match_type of 0 for an exact match

The former will return the index_pos of the last value <= criteria value in the precedent range (#N/A if criteria < smallest value)
This match_type relies upon a sorted precedent range

The latter will return the index_pos of the exact match of criteria in the precedent range (#N/A if not found)
Order of precedent range is irrelevant for this match_type

The two resulting integers are used to populate the row_num and column_num in the outer INDEX.
The INDEX will in turn return the value from the intersecting point.

See attached for a working example based on your sample

In your original A3:A11 is format as Text meaning the numbers when entered are text strings rather than numbers - hence the #N/A

OK, I think i see another wrong turn, that I made, thanks again for your help.!
I fix the cells formatted as text issue, but i didn't fix the last cell range.

One last thing, with this formula, is there also the side-effect that the cells do not update when values change except when the file is saved?