Please help w/ confusing MS Excel Functions all you smarties

[Lbelle]

please help me to understand what the FUCK the answers are to these problems...it's not homework either, just review for a test I have coming up, but I really don't understand it. I can do IF, OR AND etc. functions by themselves, but when you put them together like this I have no idea what's going on.... I'll write out the table as well as I can...be aware that I didn't come up with this table, it's just the stuff that's on the review sheet >_>.

http://www.datazap.net/sites/Lbelle/tables.JPG


ok now for the functions:
=IF(OR(B5<C5,C6=C7,D6>E7),IF(B2<D2," ",E4=456),0)

=IF(AND(B5<C5,C6=C7,D6>E7),IF(B2<D2," ".E4=456),0)

=IF(AND(C5<1000,E2>200,E5>=600),D7>500,E7>300)

=IF(OR(IF(AND(C5<1000,E2>200,E5>=600),D7>500,E7>30 0),D6>2000,D7=420),-D4,-D5)

=AND(AVERAGE(D2:D4)=333,SUM(E2:E4)>300,B5/2>600)

=IF(AND(AVERAGE(D2:D4)=333,SUM(E2:D4)>300,B5/2>600),IF(A10>0.09,"fuck",IF(D7=416.25,"Yes","No") ),"too stoned")

[Senoska]

try making it about how to cook a proper sammich and it will work.

[Jefe]

Wtf is the actual question, the meaning of the nested functions or the values it will return??

[Lbelle]

the section is called: practice with combining functions, so I'm guessing the overall value, however I'm not sure how to get to the overall value since there are like 8 functions within 1 function

[Mansin]

lulz, blunt wraps.
someone was trying to make this applicable.

[Lbelle]

yeah the guy who writes all the review sheets for this class always uses pot for his example questions

[Mizango]

yeah the guy who writes all the review sheets for this class always uses pot for his example questions

Maybe it's a subliminal hint? Get high and the equation will make sense?

Using pot examples for all of your review sheets is awesome lol. Who cares about how many cookies Mary beth sold to Steven. Blunt wraps, bongs and Dro is where its at.

[Lbelle]

I should have changed them so people would maybe help me instead of commenting on the marijuana references now I'm off to the test

[Fhqwghads]

http://blogs.salon.com/0001996/image.../11/towlie.jpg
Do you wanna get high?

[Tajin]

=IF(OR(B5<C5,C6=C7,D6>E7),IF(B2<D2," ",E4=456),0)

OR(B5<C5,C6=C7,D6>E7) :

B5<C5?
C6=C7?
D6>E7?
If any of the above is TRUE, the result of OR() is TRUE, which returnts the result of IF(B2<D2," ",E4=456)
if all of the above is FALSE, the result is 0.

IF(B2<D2," ",E4=456):
B2<D2? if TRUE, the value is " "
IF its false E4 value will be 456.(Doing E4=456 will not work to put the value in the cell though, but I'm no Excel wiz)


Something like that?

[Olo401]

Your problem is you forgot the row showing how much you spent on weed.

[Lbelle]

Tajin, yeah that's the idea. The problem is I didn't know if OR statements work the same way that IF statements do, in that you do the logic and then if it's true go to the closest thing and if false go to the farthest thing. I just took my test and I think I did okay on it...the hardest problems were about "if I move cell blahblahblah to cell blahblahblah what happens" I just completely skipped them lol . This was more of a practice test for the real test next week, so I guess more tutoring is in order for me, sigh.

[evilbau]

how did you not google this to get your answer? or use the many ways excel explains this?

An easy way to break down the problem is simply to put the nested statements in seperate cells, it will allow you to conceptualize it.

Taking the bottom one:

IF(AND(AVERAGE(D2:D4)=333,SUM(E2:D4)>300,B5/2>600),IF(A10>0.09,"fuck",IF(D7=416.25,"Yes","No")),"too stoned")

1.  IF(
2.  AND(AVERAGE(D2:D4)=333,SUM(E2:D4)>300,B5/2>600)
,
3.  IF(A10>0.09,"fuck",IF(D7=416.25,"Yes","No"))
,
4.  "too stoned"
)

Put 2 in a cell, lets say Z1. Put 3 in say Z2, 4 in Z3. Then in another cell put "=IF(Z1, Z2, Z3)" This may help if combining concepts is too overwhelming?

If you want to do it all in one go in your head, just evaluate 2. If its true, do 3, otherwise statement evaluates to 4. In a test situation you don't want to even evaluate 3 until you've evaluated 2 and made sure it is true.

If you are confused about the actual syntax of the various functions, just fire up excel and use the help, it has detailed information as well as examples. Or you can use the function builder to help break it down. Or you could just enter the cell and everything will be color coded. You should really use excel to help you understand...

IF Syntax
http://www.soul-burn.com/images/excel1.JPG

OR Syntax
http://www.soul-burn.com/images/excel2.JPG

Using Function Builder
http://www.soul-burn.com/images/excel3.JPG

Entering cell and looking at the pretty colors
http://www.soul-burn.com/images/excel4.JPG

Don't people learn order of operations in grade school?

[chewbar]

http://www.datazap.net/sites/Lbelle/tables.JPG

ok now for the functions:
=IF(OR(B5<C5,C6=C7,D6>E7),IF(B2<D2," ",E4=456),0)

=IF(AND(B5<C5,C6=C7,D6>E7),IF(B2<D2," ".E4=456),0)

This is how I would explain the solution:
It is easier to try to focus on order of operations like in algebra. So you work on innermost and then outwards. I'll do first two as an example.


For example,
=IF(OR(B5<C5,C6=C7,D6>E7),IF(B2<D2," ",E4=456),0)
becomes
=IF(OR(TRUE,FALSE,TRUE),IF(B2<D2," ",E4=456),0)
(in saving time, since B5<C5 is true, you don't have to solve the rest because whole OR statement is true, but I'm just illustrating concepts)

Then solving the OR function:
=IF(TRUE,IF(B2<D2," ",E4=456),0)
Now for what's inside the 2nd IF:
=IF(TRUE,IF(TRUE," ",TRUE),0)
(again, we don't really need to solve E4=456 if you want to save time)

Solving 2nd IF:
=IF(TRUE," ",0)

And finally solving 1st IF gives:
" "



=IF(AND(B5<C5,C6=C7,D6>E7),IF(B2<D2," ".E4=456),0)
becomes (by solving inside AND function)
=IF(AND(TRUE,FALSE,TRUE),IF(B2<D2," ".E4=456),0)
and then
=IF(FALSE,IF(B2<D2," ".E4=456),0)
(*note, since we see the first part of the first IF is false, we can skip the second IF, so answer is 0, but I'll solve it anyway)

Solving the parts of the second IF:
=IF(FALSE,IF(TRUE," ".TRUE),0)
then solving the second IF:
=IF(FALSE," ",0)
finally solving the first IF:
0

Just think of the functions as an additional set of parenthesis, and work from innermost to outermost parenthesis.

edited for clarity and typos

[chillin]

the majority of the posters in this thread are huge faggots, jesus