I need a value of x if Sheet3! W2 = Clark College and Sheet3! X2 and Y2 are blank I thought the formula below would work but it isn't. Help!!!

=IF(AND(Sheet3!W2="Clark College",ISBLANK(Sheet3!X2:Y2)),"X"," ")

Ad

Try using "count if blank"

I am assuming that you need the value to be on sheet 3

If you want the value to be on another sheet, it is more complicated, you will have to "paste special" the value to a different sheet.

Posted on Jul 22, 2010

Ad

Hi,

A 6ya expert can help you resolve that issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

The service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

Good luck!

Posted on Jan 02, 2017

Ad

You can use IF and ISBLANK. Put this formula on Sheet 1 D1:

=IF(ISBLANK(Sheet3!AM2),"x","")

You can replace "x" by any other value you need.

=IF(ISBLANK(Sheet3!AM2),"x","")

You can replace "x" by any other value you need.

Mar 04, 2010 | Microsoft Excel for PC

Let us start by setting the terminology right: To locate a point on a plane you need two coordinates ( an X-value and a Y-value). If the point is in space, you need a third coordinate which may be called z, but let's us not complicate things unecessarily.

With just two coordinates, we will be able to locate at best one point.

Let us rephrase the problem: When given two sets of coordinates, how to calculate the distance between the two points.

Let (x1,y1) and (x2,y2) be the coordinates of two points in a plane. To calculate the distance between the points, one uses the formula

d (distance)= square root of ( (x2-x1)^2+(y2-y1)^2)

When you calculate the distance you must substitute actual coordinates for X1, Y1, X2 and Y2.

As regards the bearing, I am afraid that I am no expert in maritime nor in aircraft navigation and I will not venture stray out of my area of competence. However, I know that you need an axis that defines the direction with respect to which angles are measured.

If your reference axis is the horizontal axis on a cartesian plane you can determine the angle that the line joining the points makes with that horizontal axis by calculating its cosine, then extract the arcosine.

If (X2-X1) and (Y2-Y1) are both positive then cos(theta)=(X2-X1)/d, where d is the distance (positive value) calculated above.

With just two coordinates, we will be able to locate at best one point.

Let us rephrase the problem: When given two sets of coordinates, how to calculate the distance between the two points.

Let (x1,y1) and (x2,y2) be the coordinates of two points in a plane. To calculate the distance between the points, one uses the formula

d (distance)= square root of ( (x2-x1)^2+(y2-y1)^2)

When you calculate the distance you must substitute actual coordinates for X1, Y1, X2 and Y2.

As regards the bearing, I am afraid that I am no expert in maritime nor in aircraft navigation and I will not venture stray out of my area of competence. However, I know that you need an axis that defines the direction with respect to which angles are measured.

If your reference axis is the horizontal axis on a cartesian plane you can determine the angle that the line joining the points makes with that horizontal axis by calculating its cosine, then extract the arcosine.

If (X2-X1) and (Y2-Y1) are both positive then cos(theta)=(X2-X1)/d, where d is the distance (positive value) calculated above.

Feb 27, 2010 | Casio FX-7400G Plus Calculator

Below is the formula for "2". The logic should be obvious from this so you can adapt for "1" or a combination of 1 and 2. This need to be written as one long formula so I have pasted it in to this reply so you can do a copy and paste into your spreadsheet. Sorry its not very readable in this form but you could paste into word so you can break it down and examine structure.

=IF(OR((AND(Sheet3!A12="WA",OR(Sheet3!AH2="Lewis",Sheet3!AH2="Pacific",Sheet3!AH2="Wahkiakum"))),(AND(Sheet3!A12="OR",OR(Sheet3!AH2="Clackmas",Sheet3!AH2="Multnomah",Sheet3!AH2="Washington")))),"X"," ")

=IF(OR((AND(Sheet3!A12="WA",OR(Sheet3!AH2="Lewis",Sheet3!AH2="Pacific",Sheet3!AH2="Wahkiakum"))),(AND(Sheet3!A12="OR",OR(Sheet3!AH2="Clackmas",Sheet3!AH2="Multnomah",Sheet3!AH2="Washington")))),"X"," ")

Aug 18, 2009 | Microsoft Office Excel 2007 Full Version...

This Boolean formula:

=(A2="Clark College")*(NOT(ISBLANK(B2))+(C2="Graduate"))

it will return values of 0, 1, or 2 depending upon how many of the conditions are met. This is for A AND (B OR C). [A*(B+C)]. If you mean (A AND B) OR C use the format (A*B)+C.

=(A2="Clark College")*(NOT(ISBLANK(B2))+(C2="Graduate"))

it will return values of 0, 1, or 2 depending upon how many of the conditions are met. This is for A AND (B OR C). [A*(B+C)]. If you mean (A AND B) OR C use the format (A*B)+C.

Aug 13, 2009 | Microsoft Excel 97 Full Version for PC

use if statement

=IF(AND(OR(A1="clark",A1="skamania",A1="clickitat"),B1="WA"),1,0)

where A1=T2 and B1=U2

=IF(AND(OR(A1="clark",A1="skamania",A1="clickitat"),B1="WA"),1,0)

where A1=T2 and B1=U2

Jul 16, 2009 | Microsoft Office Excel 2007

The crude solution is:

=IF(Sheet3!I2="WA",IF(Sheet3!H2="Vancouver","X",IF(Sheet3!H2="Camas","X",IF(Sheet3!H2="Ridgefield","X",IF(Sheet3!H2="Washougal","X",IF(Sheet3!H2="Stevenson","X",IF(Sheet3!H2="Hockinson","X",**"?City?"**)))))),**"?State?"**)

Where**?City?** appears when the city referenced in H2 is not part of the lookup

and**?State?** appears when something other than *WA* appears in I2.

But there is probably a better way to do this using an array of valid values like the one below:

AL NY TX WA Birmingham Albany Abilene Camas Huntsville Buffalo Galvaston Hockinson Russell New York Houston Ridgefield Stevenson Vancouver Washougal

In the scenario you could stuff the array in another worksheet and use the HLookup function to find the "Sheet3!I2" value in the first row of this array to determine which column to look in, then VLookup "Sheet3!H2" in the column of that array to see if the city referenced exists. Of course this is a much more complex formula, but it would be easily extendible without changing the formula every time.

For mor info, see "Lookup and Reference Functions" in the Excel Help.

=IF(Sheet3!I2="WA",IF(Sheet3!H2="Vancouver","X",IF(Sheet3!H2="Camas","X",IF(Sheet3!H2="Ridgefield","X",IF(Sheet3!H2="Washougal","X",IF(Sheet3!H2="Stevenson","X",IF(Sheet3!H2="Hockinson","X",

Where

and

But there is probably a better way to do this using an array of valid values like the one below:

AL NY TX WA Birmingham Albany Abilene Camas Huntsville Buffalo Galvaston Hockinson Russell New York Houston Ridgefield Stevenson Vancouver Washougal

In the scenario you could stuff the array in another worksheet and use the HLookup function to find the "Sheet3!I2" value in the first row of this array to determine which column to look in, then VLookup "Sheet3!H2" in the column of that array to see if the city referenced exists. Of course this is a much more complex formula, but it would be easily extendible without changing the formula every time.

For mor info, see "Lookup and Reference Functions" in the Excel Help.

Jun 11, 2009 | Microsoft Excel for PC

Well the symbol for greater than would be => and for lesser than would be +< so if you can incorporate that into your formula it should work. Something like A2=>Sheet3.

Jun 11, 2009 | Microsoft Excel for PC

You need to change the =IF(AND to =IF(OR

Using AND is saying both conditions must be true. Using OR is saying either condition 1 or 2 need to be true

Using AND is saying both conditions must be true. Using OR is saying either condition 1 or 2 need to be true

Apr 16, 2009 | Oracle Database Enterprise Named User Plus

You were of to a good start. Try using the AND function in the IF Formula as follows:

=IF(AND(Sheet3!H2="Clark College",ISBLANK(Sheet3!I2)),"X"," ")

=IF(AND(Sheet3!H2="Clark College",ISBLANK(Sheet3!I2)),"X"," ")

Apr 15, 2009 | Oracle Database Enterprise Named User Plus

Use the IF and Date functions together. On sheet 3, put the birthday (3/15/1954) in cell B1, put the beginning date (1/1/1946) in cell C1 and the ending date (12/31/1964) in cell D1.

Then on Sheet 1, but the following formula in the cell you want an X to be placed in.

=IF(AND(Sheet3!B1>=Sheet3!C1,(Sheet3!B1<=Sheet3!D1)),"X"," ").

Make sure you type the formula Exactly as it appears.

Then on Sheet 1, but the following formula in the cell you want an X to be placed in.

=IF(AND(Sheet3!B1>=Sheet3!C1,(Sheet3!B1<=Sheet3!D1)),"X"," ").

Make sure you type the formula Exactly as it appears.

Mar 20, 2009 | Oracle Database Enterprise Named User Plus

97 people viewed this question

Usually answered in minutes!

×