Need help to write formula in Excel

sharepoint0520
sharepoint0520 used Ask the Experts™
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.”

Can you please help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AlanConsultant

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.
Ryan ChongSoftware Team Lead

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")))

Open in new window

Subodh Tiwari (Neeraj)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")))

Open in new window


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")))

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance 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")

Author

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")))

Open in new window


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")))

Open in new window

Author

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

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial