# Nested if statement or an array formula

#### Mel Smith

##### Well-known Member
I have to set up a tracking spreadsheet and I'm trying to work out a formula for the following:

In cell P1:

1. If Cell C1 and Cell L1 are blank, then P1 is blank
2. If Cell C1 is not blank and L1 is blank, then P1 displays "Error"
3. If Cell C1 is blank and L1 is not blank, P1 dispalys "Error"

Any help or suggestions would be most appreciated.

Thanks.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Gerald Higgins

##### Well-known Member
Code:
``=IF(AND(ISBLANK(C1),ISBLANK(L1)),"","ERROR")``

#### arthurbr

##### Well-known Member
Copy following formula in P1:
=IF(AND(ISBLANK(c1);ISBLANK(l1)),"","error")

#### Mel Smith

##### Well-known Member
Perfect! Thank you.

#### paulwright7012

##### New Member
Hello all,
I think I am in the right area and hope you can help. I am trying to get the below formula to only calculate a cell that contains a number. I tries to use the if is blank function, but keep getting errors.

=IF(Antigens!\$H37<Antigens!\$J37,Antigens!\$N37,Antigens!\$M37)

Basically I want this formula to only return a result if there is a number in H37. If H37 is blank then the result should be blank. If anyone could help, I would really appreciate it.

Regards,

Paul

#### paulwright7012

##### New Member
Hello all,
I think I am in the right area and hope you can help. I am trying to get the below formula to only calculate a cell that contains a number. I tries to use the if is blank function, but keep getting errors.

<antigens!\$j37,antigens!\$n37,antigens!\$m37)
=IF(Antigens!\$H37<Antigens!\$J37,Antigens!\$N37,Antigens!\$M37)

Basically I want this formula to only return a result if there is a number in H37. If H37 is blank then the result should be blank. If anyone could help, I would really appreciate it.

Regards,

Paul</antigens!\$j37,antigens!\$n37,antigens!\$m37)

#### jtakw

##### Well-known Member
Hi,

Perhaps you should explain in detail what exactly you want the formula to do...like....if H37 is a number, then result (what result?)

#### paulwright7012

##### New Member
Hi,

Perhaps you should explain in detail what exactly you want the formula to do...like....if H37 is a number, then result (what result?)

Hi jtakw,

I apologize, not all of my formula appeared in my original question. So what I am trying to do is build a purchase requisition for my job. All the cells in column H will initially be blank, when the user populates the cell with a number my formula (see below) will put the result in the cell in column P.

=IF(Antigens!\$H15<Antigens!\$J15,Antigens!\$N15,Antigens!\$M15)

If column H is blank then I would like column P to be blank too.

#### jtakw

##### Well-known Member
Hi jtakw,

I apologize, not all of my formula appeared in my original question. So what I am trying to do is build a purchase requisition for my job. All the cells in column H will initially be blank, when the user populates the cell with a number my formula (see below) will put the result in the cell in column P.

=IF(Antigens!\$H15< Antigens!\$J15,Antigens!\$N15,Antigens!\$M15)

If column H is blank then I would like column P to be blank too.

Where in your formula do you refer to Column P???

And What's in Column J, N and M???

Last edited:

#### paulwright7012

##### New Member
Hi jtakw,
First of all thank you for your patience, I am relatively new to this forum. What I am trying to do is make a purchase requisition so all the user has to do is enter in the quantity on hand and the order quantity will populate based on the clinics Par level. I have 5 column/cells that I am working with;

Count (H26) user inserts quantity on hand here
PAR (J26) Minimum quantity the clinic should have on hand
VAR1 (M26) Calculates/Converts to minimum order quantity (Each to Box or Case)
VAR2 (N26) Calculates/Converts to order minimum quantity (ML to Bottles)
Order QTY (P26) Total quantity to order based on formula =IF(Antigens!\$H26<Antigens!\$J26,Antigens!\$N26,Antigens!\$M26)

What I am after is that when column H26 is left blank Column P26 is blank as well. When H26 has a number P26 populates the order quantity based on N26 and M26. I think your idea of IFISNUMBER is the right track but cant get it to work. This is what I tried;
=IFH26(ISNUMBER(Antigens!\$H26<Antigens!\$J29,Antigens!\$N26,Antigens!\$M26),””,))

Thank you again jtakw!

Replies
6
Views
276
Replies
3
Views
165
Replies
4
Views
2K
Replies
1
Views
112
Replies
4
Views
192

1,191,690
Messages
5,988,037
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back