formula help in excel

[Mog_73]

Hi i am trying to work out a formula to do this,

i have various fan cubic airflow capacities:
175m3 = fan 100
225m3 = fan 125A
410m3 = fan 125L

what i need to do is i have a cell which shows the M3 of a room and i need to use the result from that cell to diplay which fan i need in another cell. any ideas please?

 

[Trevor G]

Welcome to the forum,

It looks as though a VLookup would do this for you, assuming you have a master sheet with all the variables on it? You can also use a Validation so the list of M3 codes can only be selected and then the Type of Fan is displayed next to it.

 

[kidwispa]

Hi i am trying to work out a formula to do this,

i have various fan cubic airflow capacities:
175m3 = fan 100
225m3 = fan 125A
410m3 = fan 125L

what i need to do is i have a cell which shows the M3 of a room and i need to use the result from that cell to diplay which fan i need in another cell. any ideas please?

You could use this formula if you input the m3 into cell D1 as a number (ie, 125 instead of 125m3)

=LOOKUP(D1,{0,176,226},{"fan 100","fan 125A","fan 125L"})

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming that for a particular room size you have to go up to ensure the fan is big enough then you could build a lookup table like I have in columns F:G and then use a VLOOKUP formula.

Excel Workbook
	A	B	C	D	E	F	G
1	Room (m3):	180				Room (m3)	Fan
2	Fan:	fan 125A				0	fan 100
3						176	fan 125A
4						226	fan 125L
5
Choose Fan

 

[Mog_73]

Thank you all for your replies i am new to formulas except the very basic ones i.e vat calculations etc, i have seen formulas written and i was probably trying to overdo the formulas.. i was trying to write a formula probably a very long way round but if you can follow my example of what i was doing;

if a1(m3 of a room) =<175 then input 100 else if a1>175 but <225 then input 125a else if a1>225 but <410 then input 125l

basically i need to show which fan corresponds to room size as fans are based on max capacity so the fan size would depend on an 'in between' figure, i will give these formulas above a go but just wanted you to know where i believe i was going wrong.

many thanks again.

 

[Peter_SSs]

Yes, you should give the suggested formulas a try because I think they do what you want.

Assuming you are using this formula in a number of cells, the advantage of using a table in your workbook somewhere, as opposed to putting the values directly in the formula like kidwispa did, is that it is much easier to amend if you later want to change the room size values or fan names.