# Need help to write formula in Excel

on
Hi,
We have two colun in my excel. 1. Amt1 , Amt2 and i need to come up with "Output calculate column with following criteria.

if “Amt1” is 1.50 or less and “Amt2” is 2:00 or less, “Output” is “Yes.”
If “Amt1” is 1.50 or less and “Amt2” is greater than 2:00, “Output” is “No.”
If “Amt1” is greater than 1.50 and “Amt2” is 4:00 or less, “Output” is “Yes.”
If “Amt1” is greater than 1.50 and “Amt2” is greater than 4:00, “Output” is “No.”

Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consultant

Commented:
Hi,

If Amt1 is in Column A, and Amt2 is in column B, then you could try something like this:

=IF(AND(A1<=1.5,B1<=2),"Yes",IF(AND(A1<=1.5,B1>2),"No",IF(AND(A1>1.5,B1<=4),"Yes",No")))

Adjust the column letters and row numbers to suit your actual data.

Alan.

Commented:
I would use Alan's formula and revised to this instead:

``````=IF(AND(A2<=1.5,B2<=TIME(2,0,0)),"Yes",IF(AND(A2<=1.5,B2>TIME(2,0,0)),"No",IF(AND(A2>1.5,B2<=TIME(4,0,0)),"Yes","No")))
``````
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Amt2 column contains number or a time value?

If Amt2 is number...
``````=IF(OR(A2="",B2=""),"",IF(A1<=1.5,IF(B2<=2,"Yes","No"),IF(B2<=4,"Yes","No")))
``````

If Amt2 is time...
``````=IF(OR(A2="",B2=""),"",IF(A2<=1.5,IF(B2<=TIME(2,0,0),"Yes","No"),IF(B2<=TIME(4,0,0),"Yes","No")))
``````
Finance Analyst

Commented:
Your Amt1 doesn't seem to have any impact on the result, only the Amt2 changes the result.

02:00      04:00
1.5      Yes            No
10      Yes            No

So you only need to test the value of Amt2:

=IF(B2<=TIME(2,0,0),"Yes","No")

Commented:
Thank you all for quick support. I apologies for requirement change. The amt2 is the calculate  hour column from Start Date and End Date. Can you please help me to write one formula to accomplish this?

Thank you.
Calculate-Column.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Try this...

In F2
``````=IF(OR(C2="",D2="",E2=""),"",IF(C2<=1.5,IF(E2-D2<=TIME(2,0,0),"Yes","No"),IF(E2-D2<=TIME(4,0,0),"Yes","No")))
``````

OR using table structured reference...
In F2
``````=IF(OR([@Amt1]="",[@[Date/_Start]]="",[@[Date_End]]=""),"",IF([@Amt1]<=1.5,IF([@[Date_End]]-[@[Date/_Start]]<=TIME(2,0,0),"Yes","No"),IF([@[Date_End]]-[@[Date/_Start]]<=TIME(4,0,0),"Yes","No")))
``````

Commented:
Thank you Neeraj and Thank you all for your help.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with