+ Reply to Thread
Results 1 to 15 of 15

How to do two logical tests for a conditional statement

  1. #1
    Registered User
    Join Date
    01-15-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    How to do two logical tests for a conditional statement

    Hey gang,
    How do you do two "if" statements?

    I need to have two tests before i get a final result so i need to know the protocal to get two if statements.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    Belgium
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,198

    Re: How to do two logical tests for a conditional statement

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Quoting entire posts clutters the forum and makes threads hard to read !

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,905

    Re: How to do two logical tests for a conditional statement

    Check out the AND() and OR() functions in Excel Help...

    These can be nested in an IF() statement to test multiple conditions before proceeding
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Contributor
    Join Date
    04-21-2007
    Location
    Lima, Peru
    MS-Off Ver
    2000, 2007, 2010
    Posts
    674

    Re: How to do two logical tests for a conditional statement

    Hi

    Depends upon the type you require can be of the type IF(Condition 1 = True, X, Else If(Condition 2 = True, y, else z)).

    If both conditions need to be true then you can combine IF with AND to achieve your result, IF(AND(Condition 1 = true, Condition 2 = True), x, else y)

    NB The syntax above is descriptive rather than exact.

    Regards

    Jeff

  5. #5
    Registered User
    Join Date
    01-15-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to do two logical tests for a conditional statement

    So I attached the sheet as to what I'm talking about. Here is what I need, and I am far too much of a novice to know where to start.

    1) An conditional statement that sums up the box of F/G 22 in all sheets to be more than 50,000. If the sum of all those cells does not equal 50,000 or more then the answer is 0

    2) A second conditional statement that the cell of C35 must be at least .085. Once again, if this condition is not met then the result is 0

    If both statement are true then this formula prevails

    ((F32/1000)*(360))-((G32/1000)*(360)))*0.12

    The result of these statement cannot be more than 2,000,000 or 50% of all cells mentioned in 1 of this thread (the sum of F/G 22 in all sheets)

    I know this is a lot but I will take any help that I can get
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,905

    Re: How to do two logical tests for a conditional statement

    Does this work?

    Please Login or Register  to view this content.
    Note: A null (blank) is returned if the statements return false.

  7. #7
    Registered User
    Join Date
    01-15-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to do two logical tests for a conditional statement

    doing it by hand the result should be $116,433.60


    I'm at a loss

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,905

    Re: How to do two logical tests for a conditional statement

    Are you sure this formula is right?

    ((F32/1000)*(360))-((G32/1000)*(360)))*0.12

    if I plug it in it gives an error because there is an extra closing bracket before the *0.12? I guessed and removed a bracket to get my formula...

    Also, which sheet does that F32 and G32 reference.. is it the main sheet (Hallway)?

    another combo giving a different result:

    Please Login or Register  to view this content.
    using an amended version of your formula:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-15-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to do two logical tests for a conditional statement

    I got a null result for that.

    Here is what I want to do. Let me give you some more background.

    These are five locations owned by one company. In New York State you get an incentive to reduce your energy consumption. Here are the guidelines for the rules

    1) The project must be cost at least 50,000
    2) As long as the project cost is higher than 50,000, you will get 12 cents per kilowatt hour that you save.
    -Your incentive then cannot be more than 50% of the total project cost or 2,000,000

    So, this workbook is broken down into two different locations. Cheektowaga and Mt. Morris. Mt. Morris is then broken down into three different areas. We are switching out the lighting in both facilities and we want to give an accurate view of what their incentives might be if we packaged both facilities together. I will make a summary page with all of these results on them.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,905

    Re: How to do two logical tests for a conditional statement

    oh...i did not see the Cheektowaga sheet....

    Try:

    Please Login or Register  to view this content.
    But I still don't get your "hand-done" result... I get now 104924.16

    basing this on the F32 and G32 in your formula to be the Cheektowaga page references.... or are we supposed to replace those with the sum of all F32's and G32's in all pages?

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,905

    Re: How to do two logical tests for a conditional statement

    or tell me where you get the actual numbers that add up to your expected results...

  12. #12
    Registered User
    Join Date
    01-15-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to do two logical tests for a conditional statement

    First of all, it's the F22 box, not F32

    Well, F65 and G65 represent the kWh difference between the old and new technology.

    I was taking the sums of F65 on all sheets, subtracting it by the sum of all G65's and multiplying the difference by .12


    I would eventually like this formula to go into a summary sheet that would show the potential incentives.

    So my overall format for doing this for clients would be to show all end results for incentive and payback on pone summary sheet. I want to be able to do this for any and all situations. So when I plug in numbers in each individual sheet the results will appear on a summary sheet.

    Is there anyway to have this formula be transparent for any client and scenario?

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,905

    Re: How to do two logical tests for a conditional statement

    This formula you posted...showed F32 and G32, not F22...:

    ((F32/1000)*(360))-((G32/1000)*(360)))*0.12

    I don't know how F65 and G65 is now getting multiplied by 0.12...

    So then.. the formula should be sum of all F32 and G32...

    Here is another attempt using F32 and G32 from all sheets summed.....although, I am afraid we are not on the same wavelength...

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-15-2009
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: How to do two logical tests for a conditional statement

    Essentially the sum of F65's minus the sum of G65's multiplied by .12 is the same as this mess ((F32/1000)*(360))-((G32/1000)*(360)))*0.12



    This is tough, and everything you give me is giving me a null result

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,905

    Re: How to do two logical tests for a conditional statement

    Take a look at cell H1 result and formula in Cheektowaga sheet... tell me what is wrong with it.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1