[Solved] IF function with AND/OR tests

[bniccum1@cfl.rr.com]

Hello,

I'm hoping someone will have a solution for what I am trying to accomplish. I have tried numerous variations of "IF", "IF(AND(" along with "IF(OR(" arguments to no avail with consistent accuracy. I don't know if it's due to possible circular issues, the formula syntax or simply that I'm wanting something that's just not doable in OO. I've included a snapshot of the spread sheet layout.

In short, what I am trying to do is compare kWh usages from day to day with daily temperatures factored in. If the temp was lower the previous day vs today but kWh usage was higher today than the previous day, then I would like the corresponding cell in columnh "I" to result in either TRUE or FALSE. I think maybe the problem I'm having is related to a circular issue but regardless, while in this example the results accurate with respect to one set of cells but not neccesarily for the other set. Maybe the result I'm looking for just isn't achievable.

[Zizi64]

Please upload a real, ODF type sample file here.

[FJCC]

I agree a spreadsheet is needed for anyone to help efficiently. It should include at least one case where the formula does not work. As far as I can tell, all the results in your image are correct.

[me231]

i think this works for you

[FJCC]

I'm not seeing a problem with your sheet. You say

If the temp was lower the previous day vs today but kWh usage was higher today than the previous day, then I would like the corresponding cell in columnh "I" to result in either TRUE or FALSE

"temp was lower the previous day" translates to column H, which is Temp_today - Temp_yesterday, being positive.
"kWh usage was higher today than the previous day" translates to column D being positive.
Your formula

Code: Select all

=IF(AND(H7>0;D7>0);"true")

properly returns the text "true" if both conditions are met and it returns FALSE otherwise. What is not working as you expect it to?

You could simplify the formula to

Code: Select all

=AND(H7>0;D7>0)

which would return TRUE if the two conditions are met and FALSE otherwise.

[Zizi64]

i think this works for you

If you want numeric (Boolean) result, then the logical values of the function must be representated by constants 1/0 or functions TRUE()/FALSE() instead of the string "true".

[Alex1]

Your formula recognizes a correlation between temperature change and energy usage only with temperature change in one direction. Try

Code: Select all

=(G13>F13)=(C13<B13)

instead.

[me231]

j14 has boolean example - his sheet has "True". "false " text....

[bniccum1@cfl.rr.com]

Your formula recognizes a correlation between temperature change and energy usage only with temperature change in one direction. Try

Code: Select all

=(G13>F13)=(C13<B13)

instead.

Thank you! I actually got it to work but using this ...

=IF(AND(F13>G13;C13>B13);TRUE;FALSE)

Your way is much simpler and returns exactly the same results I have been looking for. Thank you to everyone who chimed in!!

[bniccum1@cfl.rr.com]

I'm not seeing a problem with your sheet. You say

If the temp was lower the previous day vs today but kWh usage was higher today than the previous day, then I would like the corresponding cell in columnh "I" to result in either TRUE or FALSE

"temp was lower the previous day" translates to column H, which is Temp_today - Temp_yesterday, being positive.
"kWh usage was higher today than the previous day" translates to column D being positive.
Your formula

Code: Select all

=IF(AND(H7>0;D7>0);"true")

properly returns the text "true" if both conditions are met and it returns FALSE otherwise. What is not working as you expect it to?

You could simplify the formula to

Code: Select all

=AND(H7>0;D7>0)

which would return TRUE if the two conditions are met and FALSE otherwise.

Thank you! Your solution also works but to get the correct result I did have to change the D13>0 to D13<0 based on the input values in cells within column D sometimes resulting in negative number. I greatly appreciate your feedback and help!!

[bniccum1@cfl.rr.com]

I'm back!! I thought I had this worked out but nope.

If the temperature today was hotter than yesterday and the kWh usage was greater, then that should be expected. But if the temperature today was cooler than yesterday and the kWh usage was greater than yesterday, that would indicate a possible issue and shouldn't be true. It would indicate that possibly something else in the home was using more power than it should be i;e; water heater, clothes dryer, refrigeration units, etc, etc. Tthe only energy consumption that should be directly affected by outsiude temperature would be the heating/cooling system and to some degree the hot water heater based on it's thermostat settings.

While the current formula I'm using is partially correct, it's not accounting for all variations. In the argument =IF(AND(F4>G4;C4>B4);1;0) the return value is "false" which is correct with respect to all arguments. But in =IF(AND(F6>G6;C6>B6);1;0) the argument is only ½ true. While the the temperature the previous day was cooler than today, the kWh usage was almost 10% higher. One would expect that given the cooler outsiude temperature on the previous day that the heating/cooling system would use less energy yet still the overall usage was higher which IMHO would indicate that issue I'm referring to.

Maybe I'm reaching for the stars here but iIt just seems to me that there must be a formula for what I'm trying to do. Maybe I just need additional data to make the argument work correctly but I'm not an expert with spreadsheets by any stretch. I've attached the spreadsheet.

[me231]

quickly responding - nested if's might be what you need...

[FJCC]

I think you want to return TRUE if (today's temperature is higher AND yesterday's energy use is higher) OR (today's temperature is lower AND yesterday's energy use is lower). That would be

Code: Select all

=IF(OR(AND(F6>G6;C6>B6);AND(F6<G6;C6<B6));1;0)

The IF() function isn't doing anything useful in that formula. You could use

Code: Select all

OR(AND(F6>G6;C6>B6);AND(F6<G6;C6<B6))

which directly returns TRUE or FALSE.

[bniccum1@cfl.rr.com]

I think you want to return TRUE if (today's temperature is higher AND yesterday's energy use is higher) OR (today's temperature is lower AND yesterday's energy use is lower). That would be

Code: Select all

=IF(OR(AND(F6>G6;C6>B6);AND(F6<G6;C6<B6));1;0)

The IF() function isn't doing anything useful in that formula. You could use

Code: Select all

OR(AND(F6>G6;C6>B6);AND(F6<G6;C6<B6))

which directly returns TRUE or FALSE.

Thank you! Both of your suggestions work and yes, the IF() function wasn't necessary at all.