I know how to explain the formula I need but I can't write it. If someone understand what im writing and can turn it into a formula it would be greatly appreciated. IF cell B is the minimum of the whole range Then take cell A to look up this page anre return the page name. IF cell B is the maximum of the whole range return page name. Else leave it blank.

As far as im concerned there should be 2 IF's and 1 Vlookup. Thanks

We'll need more specific info.

Right your IF formula looks something like this:=IF(MAX(somerange)=some_unknown_number_in_cellB1, vlookup(somewhere,using_perhaps_CellA1), IF(MIN(somerange)=some_other_unkown_number_in_cellB1, vlookup(somewhere,using_perhaps_CellA1),""))MIKE

Im supposed to use the vlookup from a different tab in the excel sheet. The range is in the whole column like the minimum of the whole column. I really don't know how else to put it. Let me know what else I can do to help you help me.

If B(min of all B column then take A to look up this page and return it here), If B(max of all the B column then take A to look up this page and return it here), Else leave it blank.This should have 1 vlookup and 2 if's

Post a small sample of your data and what it should look like before and after. See this

How Tobefore you post, it will explain how to use thepretags:http://www.computing.net/howtos/sho...

MIKE

I tried putting it on here but it does not set up correctly not everything is how it is supposed to be and if its out of order its impossible to understand. Can I send an email with the information instead?

Can I send an email with the information instead?

The object of the forums is so that we can all learn and see how the various problems are solved, sending me a private email kinda defeats the purpose.You keep saying:

This should have 1 vlookup and 2 if's

Is that a requirement?

Is this a school assignment?

Post a small sample of your data

We do not need your whole sheet,

a half dozen lines of data with the appropriate column and line headings

and how it is set up should be sufficient.What have you tried so far?

Post the formulas that you have tried.MIKE

It is not a requirement I was just told it should have 2 if's 1 vlookup. Yes it is for school it is what we are going to have for a test. These are practice problems. I've tried some formulas but I beleive they're way off and it keeps on giving me a message. I tried putting about 7 lines of the problem but it does not keep it in a row. Ill try it again. I tried again it does not come up normal. If I post it without pretags I think it will come up normal. Should I do that and seperate the numbers with like a dash or slash?

Cell A/ Cell B/ Cell C/ Cell D/ Cell E

Page ID/Page Views/Percentage/Page Name/Max/Min

28000546/ 1457170/ 49.49%/Home Page/Max

28000550/ 74880/ 2.54%/

28000911/ 11190/ 0.38%/

28000961/ 8450/ 0.29%/

28001043/ 13450/ 0.46%/

28001143/ 4010/ 0.14%/Kids 2/MinSo the I need to match the page ID with another tab in excel to ge the page name to appear on this page. Then I need to get the max or min of the percentage column to say max or min or so it leaves it blank. I couldn't get far because I don't know how to use the vlookup. I did =if(max(C:C),(min(C:C) then I dont know what to do. I hope this helps.

Cell A/ Cell B/ Cell C/ Cell D/ Cell EIn Excel, common usages is

Columnletters andRownumbersA

Cellis the intersection of a Column and Row, IE A1 or Y25 or DZ12It may sound pickey, but learn the basics now, because everything that follows builds upon your knowledge of them.

So the I need to match the page ID with another tab in excel to ge the page name to appear on this page.This is where you will need a =VLOOKUP() using your Page ID

Then I need to get the max or min of the percentage column to say max or min or so it leaves it blank.This is where you will need to know about, =IF() functions, =MAX() functions and =MIN() functions.

MIKE

I know the column and rows well. I just don't know how to use the extreme formulas like the vlookup and nested if's. I mean to me this is extereme to others it might not be. Anyway thank you for the help.

The structure of an =IF() statement is: =IF(Logical Test , Value If True , Value If False)

The three sections are separated by a comma.

The First section is some type of Logical Test, IE

A1 = A2 is a logical test, it is asking:

is the value in cell A1 equal to the value in cell A2,

it will return either True or False depending on the values in cell A1 & cell A2A1 <> X5 is a logical test, it is asking

is the value in cell A1 not equal to the value in cell X5

it will return either True or False depending on the values in cell A1 & cell X5MAX(C1:C100) = A1 is a logical test, What does the =MAX() function do?

MIN(C1:C100) <> B2 is a logical test. What does the =MIN() function do?

The second and third sections ask:

what to to IF the Value is True,

or

what to do IF the Value is FalseIf the Logical Test returns TRUE, then write the word YES

If the Logical Test Return FALSE, then write the word NO=IF(A1 = A2,"YES","NO")

=IF(Logical Test , Value If True , Value If False)

=if(A:Amax,A:Amin,"")What is wrong with the formula you posted?

MIKE

=if(A:Amax,A:Amin,"") it says NAME

Bud everything you mention above I know. What I don't know is how to set it up so I get the proper answer I know all the =IF(Logical Test , Value If True , Value If False). How do I ask it to put min or max? How do I ask it to leave it blank?

it produces the ERRORmessage #NAME?Because your formula:

=if(A:Amax,A:Amin,"")is

WRONGon a very basic level,

it does not have a Logical Test,

it doesn't even have a valid Value If True statement.

Have you taken the time to looked up what the =MAX() and =MIN() functions DO?

Have you taken the time to test them and see what results you get?

They can be used as a Logical Test as I showed in post #11MIKE

Yes Max and Min give you the Maximum or Minimum of a column

Yes Max and Min give you the Maximum or Minimum of a columnCorrect,

so if you compare the output of =MAX() or =MIN() to the value in a particular cell and you have a Logical Test.MAX(C1:C100) = A1 is a logical test,

MIN(C1:C100) <> B2 is a logical test.Using either =MAX() or =MIN() gives you the first part of your =IF() statement

Using your data:

A B C D E 1) 28000546 1457170 49.49% Home Page <Formula> 2) 28000550 74880 2.54% 3) 28000911 11190 0.38% 4) 28000961 8450 0.29% 5) 28001043 13450 0.46% 6) 28001143 4010 0.14% Kids 2In cell E1 you need a formula that begins like:

=IF(MAX($C$1:$C$6)=C1,.........

What do you display in the Value If True section?

What do think you do for the Value If False section?

Why do I use the symbol $ in the =MAX() function?

MIKE

The symbol $ is used so the cells don't continue it freezes it I know that. If true it should say max. Same goes for the min if true it should say min. If false it should leave blank. But also I need to do a formula for D to vlookup and bring the name over to D from the other excel tab

If true it should bring the name from the other excel tab if false it should leave it blank.That's column D, the =VLOOKUP section, where not that far along yet.

Where only doing column E:

Then I need to get the max or min of the percentage column to say max or min or so it leaves it blank.MIKE

I don't know what to write for it to say max or min or leave it blank

I don't know what to write for it to say max or min or leave it blankThat's what were doing.....

Using your data:

A B C D E 1) 28000546 1457170 49.49% Home Page <Formula> 2) 28000550 74880 2.54% 3) 28000911 11190 0.38% 4) 28000961 8450 0.29% 5) 28001043 13450 0.46% 6) 28001143 4010 0.14% Kids 2In cell E1 you need a formula that begins like:

=IF(MAX($C$1:$C$6)=C1,.........

What do you display in the Value If True section?

What do you think you do for the Value If False section?

~~Why do I use the symbol $ in the =MAX() function?~~MIKE

If true write max if false leave blank

If true write max if false leave blankYour half right,

What about showing the MIN value?our formula now looks like this:

=IF(MAX($C$1:$C$6)=C1,"Max",.........

What do we need to do to test for a Min value?

MIKE

=IF(MIN($C$1:$C$6)=C1,"MIN",

=IF(MIN($C$1:$C$6)=C1,"MIN",This will check for a MIN value and display the text string MIN,

but what about the MAX value and

what about the Value If False section of the =IF() statement?MIKE

i thought we already did max

i thought we already did maxWe did it partially, but you can have only one formula in a cell.

So if you need to show BOTH Max and Min what do we need to do?Remember the structure of an =IF() statement is:

=IF(Logical Test , Value If True , Value If False)

The three sections are separated by a comma.

We've worked out:

=IF(MAX($C$1:$C$6)=C1,"Max",

and

=IF(MIN($C$1:$C$6)=C1,"MIN",

but both are not complete formulas.

Both formula are missing the Value If False section.

And since you can have only one formula in a cell, what do we do?

MIKE

if false we put ""

if false we put ""Still does not answer the question of how do we do TWO tests (one for MAX and one for MIN) in a single formula.

MIKE

I dont know

You need to combine both tests in one formula. So if we have the test for MAX and MIN partially worked out,with either one needing a Value If False section:

=IF(MAX($C$1:$C$6)=C1,"Max",

and

=IF(MIN($C$1:$C$6)=C1,"MIN",

How would you combine the two statments into one?

An =IF() statment can be used in a Value section.

and we need a Value If False section.Somthing along the lines of this should work:

=IF(MAX($C$1:$C$6)=C1,"Max",IF(MIN($C$1:$C$6)=C1,"MIN",""))

MIKE

That works. Thank you

Did you put the formula in cell E1 and drag down so that the formula is in each cell from E1 thru E6? MIKE

Yes I did. I dont know how to do the Vlookup though but I have to leave in 5 mins to go to class. Thank you very much I at least got the first part

The teacher explained it in class. Thank you

I have been racking my brain trying to incorporate an if statement with two vlookups, to provide a Yes or No. What I am trying to do is, return a Yes in the Worthy column if either the Material is found on a separate spreadsheet or the program combination is found in a separate spread sheet, return a Yes if found, otherwise a No. I do not want a #n/a, as I use the yes/no for other calculations.

I have tried if statements, or statements, if(or statements, but cannot get them to work.The first spreadsheet is the working sheet, the 2nd spreadsheet is the tab i am looking up the material on, and the third spreadsheet is the program data. Need help

Sold To Material Program Worthy ?

1000001422 3FE23086AA 1000001422/3FE23086AA #N/A

1000001422 108994989 1000001422/108994989 Yes

111111 408837490 111111/408837490 Yes

Part Number Status

3FE23086AA Yes

3FE25676BA Yes

Program Status

1000000894/109199067 Yes

1000000894/109313197 Yes

Thanks

Steve

Spro78, please ask your question in a new thread. Also read this How-To to learn how to use the PRE tags so all your data lines up neatly when you post.

http://www.computing.net/howtos/sho...

As for your #N/A problem, try looking up, in the Excel Help files: =ISNA()

MIKE

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History