Excel if function?

richmond32 · 02-09-2015 2:23pm #1

I have no macro or advanced Excel skills.

I have a dash board for a project provided by somebody else, with a macro enabled in it with click and drag sliding cells to increase/reduce costs/income.

I have a table as per below on tab 4 and want to link the cells to the front tab, showing if water speed is 2 m/s than the output will be x (as per the table below) etc, this will need to change depending on the rotor size as per the table also. For example if speed is 2 m/s and rotor size is 5 metres, output would be 28KW. I hope that makes sense. The front page has a click and drag macro and what I want is that if the cell g37 shows 2 m/s and cell g39 shows 5 metre rotor, then cell g41 will show output of 28. A formula will not work as the outputs do not increase proportionally

Machine Class Rotor size 2 m/s 3 m/s 4 m/s
Metre kW kW kW

5 m class 5 28 95 224
7 55 190 450
7 m class 10 115 385 920
11 138 470 1100

Any ideas would be gratefully accepted?

Gordon · 02-09-2015 2:43pm

Not really sure exactly what you mean, are you able to attach a small version of what you are looking at?

richmond32 · 02-09-2015 2:52pm

Sorry, the table in my op lost it formatting, there are three variables on the top (horizontal) 2/3/4 kw and 4 on the vertical, ie rotor size 5,7,10,11. there are 12 results on the table giving different random results.

I am not sure how to attach a version as mentioned, don't come on here too often I'm afraid. maybe below will be better

Rotor size 2 m/s 3 m/s 4 m/s
M kW kW kW

5 28 95 224
7 55 190 450
10 115 385 920
11 138 470 1100

Gordon · 02-09-2015 2:59pm

To attach a file, ensure you're on the desktop site (not mobile site) and click advanced reply. Below the message box and Post Icons there's an 'Attach files' section where you can upload an xls file.

Might want to clear any sensitive data first!

richmond32 · 02-09-2015 3:15pm

Thanks Gordon , must be going blind, only see post icons and thread subscription and rate thread in advanced options, would it be because I am limited user of the site?

I am on a desktop

Jayd0g · 02-09-2015 3:18pm

Go Advanced reply, then scroll down to below the reply button.

Should see options to manage attachments.

Gordon · 02-09-2015 3:29pm

Sorry, just realised you've only got a few posts so you can't attach files!

Can you upload to dropbox or summat? I don't really understand what you mean, but maybe others do.

Talisman · 02-09-2015 3:31pm

What you are describing is a two-way lookup. Data is retrieved from the Lookup table based on looking up a value in the top row (water speed) and a value in the first column (rotor size).

You need to use the MATCH and INDEX functions.

How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel

richmond32 · 02-09-2015 4:07pm

Wont allow me to put in a URL with link to dropbox, as I am a new user.

I can e mail link if that is allowed

Gordon · 02-09-2015 4:14pm

Heh, sorry, you can't post links either! I've PMd you with my email address if you want to send it on and I'll attach it.

It does sound like an index/match thing, which is why I was interested as I've just done something similar.

For example, if you have a column with stuff in column A, and it has something in column B that you want to retrieve, and something in C1 that is the thing you want to match with column A, try this:

=INDEX(B:B, MATCH(C1, A:A, 0))

A - B - C
Ford - 500hp - Ford

That will give you 500hp for Ford in column C as it looks at column A for 'Ford' and gives you the value for column B (first row in this example).

Gordon · 02-09-2015 4:32pm

Here's the excel file, I'm unable to open it directly from Mac Office, but can in Open Office. Will have a look later to see if I can understand it, but there's quite a few macros there to dig through seemingly.

If others can have a look - be warned there are macros, but judging by richmond32's email, he is posting from a genuine company, so can't see him inserting malicious code fwiw.

PrzemoF · 02-09-2015 5:37pm

I don't see any macros in that file. I don't think you can have macros in xlsx. Save it as xlsm

Gordon · 04-09-2015 2:37pm

OK, think I got it.

First you need to amend the generation table.

In row 4 there, take away the 'm/s' and just have actual numbers: 2, 3, 4 etc

Then in column A again just use numbers, so:
5
7
etc

Now put this code in dashboard G41 cell:

=VLOOKUP(Dashboard!G39, generation!A7:F10, MATCH(Dashboard!G37, generation!A4:F4,0),FALSE)

It should find the appropriate item in the table.

Gordon · 04-09-2015 2:38pm

PrzemoF wrote: »

I don't see any macros in that file. I don't think you can have macros in xlsx. Save it as xlsm

Aye good point, not sure why Open office hinted at there being macros in it!

Excel if function?

Comments