[ChadE]

Ok so I thought i was pretty good at excel until this shit...does anybody on here under stand the "IF" function? I cant figure out these formulas for shit or even how to enter it in to the wizard for that matter. Heres one of the questions if somebody can tell me what to enter into the wizard or the formula to use as an example to get going for the rest of them i would greatly appreciate it.

1. Write an IF function for cell H3 that assigns the value of cell A7 to cell H3 if the value in cell J7 is less than the value in cell Q2; otherwise, have the IF function assign zero (0) to cell H3


None of the cells have values...all its looking for is the forumula to execute so i guess you just make up values? That was my take on it...

Thanks in advance for anyone who can help with this

[jhv]

WTF
idk what youre talking about sorry

i think my dad used to work with excel or still does, one or the other; next time i see him ill ask him for you

[Ninjaneer]

How much will you pay me to do your homework?

It's quiet simple really. The help file has good examples to get you started too but I'll explain here... bear with me if this is too geeky for you, but I'm setting you up for life... give a man a fish etc.

First off, yes you need to populate the cells with values to test it out since they're not already given to you.

To enter an IF statement you go to a cell and first type an equal (=) sign, followed by the "IF" statement, and follow that with an opening parenthesis, the ( symbol. The nice thing about Excel is that once you enter "=IF(" into a cell, it will show you what parameters are required of you and what to enter to complete the formula... so you'll see it show: IF(logical_test, [value_if_true], [value_if_false])

logical_test = the comparison you want to make... in this case, that J7 is less than Q2. The logical notation for this is simply: J7 < Q2.

[value_if_true] = the value you want to return / display if the logical_test is TRUE. If J7 is truly less than Q2, you want to display A7.

[value_if_false] = the value to display if logical_test is FALSE. In this case, zero.

Make sense now? So putting it all together we get the following:

=IF(J7 < Q2,A7,0)

Enter the above IF statement into cell H3. Voila!

For testing purposes you can enter the following values:
A7 = 5
J7 = 8
Q2 = 10

H3 would then show the value of A7, which is 5, since J7 < Q2 is true.

Now change J7 to 20, so now H3 shows 0 since J7 < Q2 is not true (ie. false).

[ChadE]

Wow thanks alot...that really helps. I read the help files and ive read over it in the book but it just wants clicking. Thanks for your help. I am now going to attempt to finish this lol. I may be back with more ?'s I have 8 more to go lol

[SoYank]

Originally Posted by ChadE

Ok so I thought i was pretty good at excel until this shit...does anybody on here under stand the "IF" function? I cant figure out these formulas for shit or even how to enter it in to the wizard for that matter. Heres one of the questions if somebody can tell me what to enter into the wizard or the formula to use as an example to get going for the rest of them i would greatly appreciate it.

1. Write an IF function for cell H3 that assigns the value of cell A7 to cell H3 if the value in cell J7 is less than the value in cell Q2; otherwise, have the IF function assign zero (0) to cell H3


None of the cells have values...all its looking for is the forumula to execute so i guess you just make up values? That was my take on it...

Thanks in advance for anyone who can help with this

Something to keep in mind, too, is that the IF TRUE and IF FALSE parameters don't have to be numbers or cells but can also be other formulas that reference other cells, i.e., nesting. For example, instead of putting 0 in cell A7 if the value of J7 is greater than Q2 (logical test fails, returns FALSE), put the maximum value from a range of cells. So, if the range is A8 to A15, your formula would be =IF(J7<Q2,A7,MAX(A8:A15)). The colors are for illustrative purposes only.

BTW, the IF is a function already. You don't need to write it again. What you're writing is a formula that uses the function.

[ChadE]

Ok so i figured out all the questions about Nested and the other IF function ?s now im stuck on this one...

Write cell G3 as a relative cell reference, absolute cell reference, mixed cell reference with the row varying, and mixed cell reference with the column varying.

Example for this anyone? This book sucks at explaning this chapter.

[ChadE]

Bump for the night..hopefully ill have some sort of an answer in the morn...this shits due tommorw night

While im at it i came across one more questions

Write a formula for the cell B8 that divides cell D5 by the sum of cells N10 through N13. Write the formula so that when it is copied to cells C8 and D8 cell D5 remains absolute.

TIA

[ChadE]

one last bump before

[SoYank]

Originally Posted by ChadE

While im at it i came across one more questions

Write a formula for the cell B8 that divides cell D5 by the sum of cells N10 through N13. Write the formula so that when it is copied to cells C8 and D8 cell D5 remains absolute.

TIA

What's so difficult about that last question?

In B8, enter: =D5/SUM(N10:N13)

A hint for the last part (and for the initial question you posed): if you send me some $ I'll tell you.
A more direct hint for you: Press F1. In the box at the top type "absolute reference". Go from there.

[wrestleSBA]

Originally Posted by ChadE

Ok so i figured out all the questions about Nested and the other IF function ?s now im stuck on this one...

Write cell G3 as a relative cell reference, absolute cell reference, mixed cell reference with the row varying, and mixed cell reference with the column varying.

Example for this anyone? This book sucks at explaning this chapter.

Ok this is simple enough, I am going to assume that G 3 references A1:

relative
=A1

absolute
=$A$1

mixed-row varying
=$A1

mixed-column varying
=A$1

Write a formula for the cell B8 that divides cell D5 by the sum of cells N10 through N13. Write the formula so that when it is copied to cells C8 and D8 cell D5 remains absolute

=$D$5/SUM(N10:N13)

Hope those help. Key thing to remember here is that it is the $ which denotes an absolute reference.

[SoYank]

Damn, wrestleSBA, don't feed him! Teach him to fish!



BTW, this is NOT intended as a flame.

[ChadE]

I greatly appreciate everybodys help...by the way SoYank i still have plenty of fishing to do lol.

[Ninjaneer]

Ah cool. Was about to come back and reply, been busy all day, glad you got your answers

[SoYank]

Originally Posted by ChadE

I greatly appreciate everybodys help...by the way SoYank i still have plenty of fishing to do lol.

Good, but would you please use that damned fly as your bait?

[ChadE]

So let me see i f i got this right...heres another questions.

Write the formula for cell Y6 that divides cell P7 by the sums of cells H4 I4 J4. Write the formula so that when it is copied to cells Y7 Y8 Y9, cell P7 remains absolute

This is what i got: =$P$7/(H4+I4+J4)

Am i getting this now?

[Ninjaneer]

Originally Posted by ChadE

So let me see i f i got this right...heres another questions.

Write the formula for cell Y6 that divides cell P7 by the sums of cells H4 I4 J4. Write the formula so that when it is copied to cells Y7 Y8 Y9, cell P7 remains absolute

This is what i got: =$P$7/(H4+I4+J4)

Am i getting this now?

Looks right. Instead of individually adding each cell - especially if there are lots of cells to work with - you can use the SUM function:

=$P$7/SUM(H4:J4)

Here SUM will sum up all the cells from H4 to J4.

BTW, yesterday at work I saw your sig and it actually made me touch my screen... I was like wth is this thing... oh!

[SoYank]

Originally Posted by Ninjaneer

Looks right. Instead of individually adding each cell - especially if there are lots of cells to work with - you can use the SUM function:

=$P$7/SUM(H4:J4)

Here SUM will sum up all the cells from H4 to J4.

BTW, yesterday at work I saw your sig and it actually made me touch my screen... I was like wth is this thing... oh!

Ninjaneer is correct. However, the sum function only works with a range of adjacent cells, vertically or horizontally. If you want to sum non-adjacent cells such as cells A8, A10, A12, you'd need to use the + as ChadE did with his attempt. You also would be able to use =SUM() for non-adjacent ranges. For example, to sum B2 through B7 and E2 through E7 and G2 through G7 into A1, the formula in cell A1 would be =SUM(B2:B2,E2:E7,G2:G7). Further, if you wanted to sum all the cells in a multi-column, multi-row area the formula you'd use would be =SUM(<top left cell>:<bottom right cell>). Remember you can use absolute and/or relative reference, too.

[ChadE]

Originally Posted by SoYank

Ninjaneer is correct. However, the sum function only works with a range of adjacent cells, vertically or horizontally. If you want to sum non-adjacent cells such as cells A8, A10, A12, you'd need to use the + as ChadE did with his attempt. You also would be able to use =SUM() for non-adjacent ranges. For example, to sum B2 through B7 and E2 through E7 and G2 through G7 into A1, the formula in cell A1 would be =SUM(B2:B2,E2:E7,G2:G7). Further, if you wanted to sum all the cells in a multi-column, multi-row area the formula you'd use would be =SUM(<top left cell>:<bottom right cell>). Remember you can use absolute and/or relative reference, too.

Just out of curiosity....since you are so knowledgeable on this what do you do for a living? Do you use excel alot?

[SoYank]

Originally Posted by ChadE

Just out of curiosity....since you are so knowledgeable on this what do you do for a living? Do you use excel alot?

I use Excel every day in some manner.

[jeveryday]

Originally Posted by ChadE

Wow thanks alot...that really helps. I read the help files and ive read over it in the book but it just wants clicking. Thanks for your help. I am now going to attempt to finish this lol. I may be back with more ?'s I have 8 more to go lol

Wow spoken like a true educated guy.

[Hawkeye]

Originally Posted by ChadE

Just out of curiosity....since you are so knowledgeable on this what do you do for a living? Do you use excel alot?

I think a lot of people on here use Excel for a living. Personally I use it about 6 hours a day for my job (on here the other two )