Calculate a value based on reference data and conditions for Google Sheets

[Chrizh]

Hello!

Thanks in advance for any help with this, really appreciate it.

I have a spreadsheet (see attached) which has a cross reference table of percentages. The percentages are modifiers to acceleration, top speed and handling statistics of a certain vehicle on certain tracks i.e. a Sports vehicle on an Off-Road track will have a 40% reduction to acceleration, 10% reduction to top-speed and 40% reduction to handling. The reference data for that example is shown in cells I5, J5, K5 as 60%, 90%, 60%.

I'm looking to have a formula which calculates the effect of the modifiers on user entered numbers. For example, the user selects vehicle and track types from drop down lists and then enters the values 400 acceleration, 400 top-speed and 200 handling. The formula would then put the calculated modified stats (240, 320 and 160 respectively) in to other cells.

I know the formula for calculating those individually would simply be the entered number multiplied by the modifier percentage. I'm just not sure how to wrap a formula around that which does all the clever look up and conditions!

Thanks for your time.

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2			Track Type
3			Sport			Urban			Off-Road			Universal
4		Vehicle Type	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling
5		Sport	100%	100%	100%	90%	90%	90%	60%	90%	60%	100%	100%	100%
6		Urban	100%	100%	90%	110%	110%	110%	80%	80%	80%	100%	100%	100%
7		Off-Road	100%	100%	90%	90%	90%	90%	110%	110%	120%	100%	100%	100%
8		Universal	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
9
10
11				Acceleration	Top Speed	Handling
12		Vehicle Type	Track Type	400	400	200	Base Stats
13		Sport	Off-Road	240	360	120	Modified Stats
14
Sheet1

Cell Formulas
Range	Formula
D13:F13	D13	=D12*I5

Cells with Data Validation
Cell	Allow	Criteria
B13	List	=$B$5:$B$8
C13	List	=$C$3:$N$3

 

[KRice]

Welcome to the MrExcel board! Can we focus on this point?

i.e. a Sports vehicle on an Off-Road track will have a 40% reduction to acceleration, 10% reduction to top-speed and 40% reduction to handling

When you mention a percentage reduction, what is the starting basis for that reduction? I'm assuming all of these percentage reductions are relative to a vehicle's performance on a "Universal" track type (which are all 100%) and that when a user enters "base stats" they are for the Universal track type. Is this correct?

If it is, then I would recommend for your table that you introduce a helper row (shown on row 4) that includes the track type above each performance characteristic column. The reason is to facilitate matching with a formula. I generally avoid merged cells (which is what you have in the row 3 column headings). When attempting to "find" the Sport track/Handling column (column E), a formula will need to identify column E as "Sport" and since you've merged cells on row 3, there is no "sport" label to be found in column E. The helper row resolves this problem. After creating the helper row, hide it from view. Then the formula shown in this example will perform the two-way lookup to pull the performance multiplier from the data table.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2			Track Type
3			Sport			Urban			Off-Road			Universal
4			Sport	Sport	Sport	Urban	Urban	Urban	Off-Road	Off-Road	Off-Road	Universal	Universal	Universal
5		Vehicle Type	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling
6		Sport	100%	100%	100%	90%	90%	90%	60%	90%	60%	100%	100%	100%
7		Urban	100%	100%	90%	110%	110%	110%	80%	80%	80%	100%	100%	100%
8		Off-Road	100%	100%	90%	90%	90%	90%	110%	110%	120%	100%	100%	100%
9		Universal	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
10
11
12				Acceleration	Top Speed	Handling
13		Vehicle Type	Track Type	400	400	200	Base Stats
14		Sport	Off-Road	240	360	120	Modified Stats
Sheet1

Cell Formulas
Range	Formula
D14:F14	D14	=D13*INDEX($C$6:$N$9,MATCH($B14,$B$6:$B$9,0),MATCH(1,($C$4:$N$4=$C14)*($C$5:$N$5=D$12),0))

 

[Chrizh]

Welcome to the MrExcel board! Can we focus on this point?

When you mention a percentage reduction, what is the starting basis for that reduction? I'm assuming all of these percentage reductions are relative to a vehicle's performance on a "Universal" track type (which are all 100%) and that when a user enters "base stats" they are for the Universal track type. Is this correct?

Hello, thanks for the tips and reply That's really useful to know about the merged cells.

I'll try to elaborate a bit more. The vehicles in the game have a set of base statistics (acceleration, speed, handling). It's irrelevant to a degree, but these base stats can be upgraded. The vehicles are categorised by a type, sport, urban, off-road or universal. The tracks they race are are also categorised the same. Races are randomly generated. For example you might have a race where only sports vehicles are allowed to race but the track may be an urban track. Or a race where only off-road vehicles are allowed to race but the track may be a sports track. Each permutation has modifiers as shown in the cross reference cells B3:N9. Whatever vehicle is used, whatever it's 'base stats' are, are affected by the modifiers. So I guess you could say that the base stat of that vehicle is 100% and the modifiers are relevant to that. So where the cross reference table says 100%, then there is no effect to the vehicles base stats. 110% would mean the vehicles base stat for that particular stat, is increased by 10%. And so on.

I don't know if that makes it any clearer! If it's of any interest, the mobile game is called SpotRacers, it's available on Android and Apple.

I will try your formula suggestion now, thank you!

 

[KRice]

Thanks for the explanation. Based on what you've described, it sounds like there are no issues. I was asking because I noticed some performance multipliers greater than 1 (e.g., 110%) and wasn't sure if additional adjustments to a user's input "base stats" were necessary. But it sounds like the input base stats represent the full (100$) baseline...which is what the formulas in the "modified stats" line assume.

 

[Chrizh]

Thanks for the explanation. Based on what you've described, it sounds like there are no issues. I was asking because I noticed some performance multipliers greater than 1 (e.g., 110%) and wasn't sure if additional adjustments to a user's input "base stats" were necessary. But it sounds like the input base stats represent the full (100$) baseline...which is what the formulas in the "modified stats" line assume.

Thanks for the reply. I've tried the formula but for some reason it produces an #N/A error. I've probably done something wrong!

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2			Track Type
3			Sport			Urban			Off-Road			Universal
4			Sport	Sport	Sport	Urban	Urban	Urban	Off-Road	Off-Road	Off-Road	Universal	Universal	Universal
5		Vehicle Type	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling	Acceleration	Top Speed	Handling
6		Sport	100%	100%	100%	90%	90%	90%	60%	90%	60%	100%	100%	100%
7		Urban	100%	100%	90%	110%	110%	110%	80%	80%	80%	100%	100%	100%
8		Off-Road	100%	100%	90%	90%	90%	90%	110%	110%	120%	100%	100%	100%
9		Universal	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
10
11
12				Acceleration	Top Speed	Handling
13		Vehicle Type	Track Type	400	400	200	Base Stats
14		Sport	Off-Road	#N/A	#N/A	#N/A	Modified Stats
15
Sheet1

Cell Formulas
Range	Formula
D14:F14	D14	=D13*INDEX($C$6:$N$9,MATCH($B14,$B$6:$B$9,0),MATCH(1,($C$4:$N$4=$C14)*($C$5:$N$5=D$12),0))

Cells with Data Validation
Cell	Allow	Criteria
B14	List	=$B$6:$B$9
C14	List	=$C$3:$N$3

 

[Chrizh]

Ignore the above. I tried it on Google Sheets (it's Sheets I'm using for the main spreadsheet) and it appears to work. I'm assuming this is something to do with Excel 2019.

Really appreciate your help with this, thank you.

 

[KRice]

You're welcome...I'm happy to help. I'm puzzled why this is giving an error for you in Excel...there should be no issues with the formula in Excel 2019. Often the #N/A error occurs with the MATCH function when no match can be found, but in your case, your inputs B14:C14 use data validation to pull content directly from the Vehicle Type row labels and Track Type column headings, so it would be unlikely for there to be a mismatch. Sometimes an extra space or hidden character resides in one of the cells that needs to be matched (a common source of the error when it visually appears that a match should be found). If you want to investigate this further, select cell D14 for the Sport/Off-Road/Acceleration modified stats cell where your first #N/A appears and go into the formula bar.

You'll see this formula:

=D13*INDEX($C$6:$N$9,MATCH($B14,$B$6:$B$9,0),MATCH(1,($C$4:$N$4=$C14)*($C$5:$N$5=D$12),0))

Select with the mouse MATCH($B14,$B$6:$B$9,0) and hit F9 and you should see this part of the formula evaluate to the row index of interest where the vehicle type is found in your reference table. That appears in the 1st row of the data block in the reference table, so it should return a 1.
While still in the formula bar, select ($C$4:$N$4=$C14) and hit F9 and you should see an array showing where the columns containing the Off-Road track type are found...and it should look like this:
{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}
While still in the formula bar, select ($C$5:$N$5=D$12) and hit F9 and you should see an array showing where the columns containing Acceleration are found...and it should look like this:
{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}
Or you can take a shortcut and highlight both of these ($C$4:$N$4=$C14)*($C$5:$N$5=D$12), and when the TRUE/FALSE arrays are multiplied together, the TRUE's are coerced to 1's and the FALSE's to 0's, and the result is an array that looks like this: {0,0,0,0,0,0,1,0,0,0,0,0}. This means the column of interest is the 7th one in your reference table.

If you dissect the formula like this, I would expect that one of these arrays will show something unusual or the MATCH function will return an error, and that will tell you where to look in your reference table to identify the cause of the problem. One caveat: after exploring a formula like this by hitting F9, do not hit enter, as that will hard-wire the exposed result/array into the formula. It is safer to simply hit Esc to exit the formula editing mode without committing any changes to the formula.

Here is an example of what my formula bar looks like if I select ($C$4:$N$4=$C14)*($C$5:$N$5=D$12) and hit F9...at which point the array of 1's/0's appears:

 

[Chrizh]

You're welcome...I'm happy to help. I'm puzzled why this is giving an error for you in Excel...there should be no issues with the formula in Excel 2019. Often the #N/A error occurs with the MATCH function when no match can be found, but in your case, your inputs B14:C14 use data validation to pull content directly from the Vehicle Type row labels and Track Type column headings, so it would be unlikely for there to be a mismatch. Sometimes an extra space or hidden character resides in one of the cells that needs to be matched (a common source of the error when it visually appears that a match should be found). If you want to investigate this further, select cell D14 for the Sport/Off-Road/Acceleration modified stats cell where your first #N/A appears and go into the formula bar.

You'll see this formula:

=D13*INDEX($C$6:$N$9,MATCH($B14,$B$6:$B$9,0),MATCH(1,($C$4:$N$4=$C14)*($C$5:$N$5=D$12),0))

Select with the mouse MATCH($B14,$B$6:$B$9,0) and hit F9 and you should see this part of the formula evaluate to the row index of interest where the vehicle type is found in your reference table. That appears in the 1st row of the data block in the reference table, so it should return a 1.
While still in the formula bar, select ($C$4:$N$4=$C14) and hit F9 and you should see an array showing where the columns containing the Off-Road track type are found...and it should look like this:
{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}
While still in the formula bar, select ($C$5:$N$5=D$12) and hit F9 and you should see an array showing where the columns containing Acceleration are found...and it should look like this:
{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}
Or you can take a shortcut and highlight both of these ($C$4:$N$4=$C14)*($C$5:$N$5=D$12), and when the TRUE/FALSE arrays are multiplied together, the TRUE's are coerced to 1's and the FALSE's to 0's, and the result is an array that looks like this: {0,0,0,0,0,0,1,0,0,0,0,0}. This means the column of interest is the 7th one in your reference table.

If you dissect the formula like this, I would expect that one of these arrays will show something unusual or the MATCH function will return an error, and that will tell you where to look in your reference table to identify the cause of the problem. One caveat: after exploring a formula like this by hitting F9, do not hit enter, as that will hard-wire the exposed result/array into the formula. It is safer to simply hit Esc to exit the formula editing mode without committing any changes to the formula.

Here is an example of what my formula bar looks like if I select ($C$4:$N$4=$C14)*($C$5:$N$5=D$12) and hit F9...at which point the array of 1's/0's appears:
View attachment 70534

Hi again, I cannot work this out. Curiosity got the better of me so I followed your steps above and the results are the same as yours. The evaluations make sense, so I'm not sure what the problem is!

Don't know if it helps but If I use the Excel formula evaluator on D14 it says:

400*INDEX($C$6:$N$9,1,MATCH(1,0,0))

"The next evaluation will result in an error"

I click evaluate and get:

400*INDEX($C$6:$N$9,1,#N/A)

 

[KRice]

That’s odd…can you post the two arrays that you get that when multiplied together give you a 0 for the 2nd term in the last MATCH function. You should see two arrays of Trues and Falses when you evaluate them with F9 in the formula bar. Your can copy them to your clipboard (Ctrl C) directly from the formula bar and then paste them into your post.

 

[Chrizh]

That’s odd…can you post the two arrays that you get that when multiplied together give you a 0 for the 2nd term in the last MATCH function. You should see two arrays of Trues and Falses when you evaluate them with F9 in the formula bar. Your can copy them to your clipboard (Ctrl C) directly from the formula bar and then paste them into your post.

Hello again. This is what I get:

=D13*INDEX($C$6:$N$9,2,MATCH(1,{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}*{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE},0))