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

 

[Fluff]

tried it on Google Sheets (it's Sheets I'm using for the main spreadsheet)

In future please make this clear in your original post & such questions should be posted in the General Discussion & Other Applications
I have done this for you this time.

 

[Chrizh]

In future please make this clear in your original post & such questions should be posted in the General Discussion & Other Applications
I have done this for you this time.

I understand. However it was Excel I was working with. I just happen to copy what I produce in Excel to Sheets.

 

[Fluff]

Ok, but not everything that works in Excel will work in Sheets.

 

[KRice]

Select cell D14 and then click in the formula bar, then hit F2 to enter edit mode and then re-commit the formula by hitting Ctrl-Shift-Enter simultaneously…sometimes abbreviated CSE. You should see curly brackets inserted around the formulas in the formula bar, which means the formula is treated as an array formula. Does that result in an expected output? If so, drag the cell D14 across to populate E14:F14.

I had a look at the worksheet in Sheets and noticed that during the conversion, Sheets prefaced the formulas with ArrayFormula…which typically means that the formula needs to be entered in Excel with CSE.

 

[Chrizh]

Select cell D14 and then click in the formula bar, then hit F2 to enter edit mode and then re-commit the formula by hitting Ctrl-Shift-Enter simultaneously…sometimes abbreviated CSE. You should see curly brackets inserted around the formulas in the formula bar, which means the formula is treated as an array formula. Does that result in an expected output? If so, drag the cell D14 across to populate E14:F14.

I had a look at the worksheet in Sheets and noticed that during the conversion, Sheets prefaced the formulas with ArrayFormula…which typically means that the formula needs to be entered in Excel with CSE.

This was indeed the problem. Thank you for your continued help, much appreciated.

 

[KRice]

I'm happy to help. I'm glad the mystery is now solved. You were right when you suspected the issue might have some to do with Excel 2019. Excel 365 natively handles array formulas without any special treatment, but earlier versions require entering array formulas with Ctrl-Shift-Enter.