How to do two logical tests for a conditional statement

[pjgodios]

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.

[arthurbr]

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!

[NBVC]

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

These can be nested in an IF() statement to test multiple conditions before proceeding

[solnajeff]

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

[pjgodios]

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

[NBVC]

Does this work?

Please Login or Register  to view this content.

Note: A null (blank) is returned if the statements return false.

[pjgodios]

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


I'm at a loss

[NBVC]

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.

[pjgodios]

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.

[NBVC]

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?

[NBVC]

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

[pjgodios]

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?

[NBVC]

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.

[pjgodios]

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

[NBVC]

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