I am taking an online Excel class. It's been a breeze so far, most of the stuff is easy and I pick up comptuer stuff quickly. However I am having some issues learning the IF, VLOOKUP and HLOOKUP. I'm using the function wizard but I can't figure out what information to plug into what sections.

The little tips aren't helping either.

Example, on the IF function I can't figure out what the heck the "Logical_test" is. I've pretty much got "If this, then that", but the function wizard is really confusing.

Yes, I have a book and tutorials to work with, but they don't explain what goes where very well. I was going to meet with my instructor this week but I've got a bad cold and don't want to give it to her.

Can anyone put it in English for me?

From Microsoft's help (I don't like Wizards)


IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE.

Value_if_true is the value that is returned if logical_test is TRUE. If logical_test is TRUE and value_if_true is omitted, TRUE is returned. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. If logical_test is FALSE and value_if_false is omitted, FALSE is returned. Value_if_false can be another formula.

Logical_test is what you're trying to find to be TRUE or FALSE. Such as "A1>3" would mean "If the contents of cell A1 are greater than 3..." This would result in either a TRUE or a FALSE answer.

Example: =IF(A1>3, "High", "Low")
Means: If the contents of cell A1 are greater than 3 then the contents of the cell this function is in should say High. If not, then it should say Low.

In the Help menu go to Contents and Index. In the Index tab you can enter any term you want to search for and you'll get a description of that function and sometimes it will provide examples. You can use that to lookup VLOOKUP and HLOOKUP.

The "logical_test" part is just the part you'd put between the "if" and the "then" if you were saying it in English. So if you wanted to say, if the value in cell C1 is less than 3, then put an A in cell D1, otherwise put a Z, cell D1 would read =IF(C1<3,"A","B")
the c1<3 is the logic test part.

I generally (probably never in a workbook I've developed) do not use the lookup functions, so can't help you there.

(Ok, Bill beat me, but I think we said the same thing)

The vlookup feature can be used when you have a graduated price scale, like an income tax table or a volume discount.

ie: If you make 1-19 copies, they're $.06 each, if you make 20-99 copies, they're $.05 each, if you make 100-500 copies, they're $.04 each, etc.

For this example, you'd enter the minimum number of copies in one column with the corresponding price in the second column.
1 ... .06
20 ... .05
100 ... .04

In another area of your spreadsheet, or on another page in the same file, you'd list the number of copies made and want it to look up the price for you. Say you made 75 copies.

You don't need to include the ranges of copies in your lookup table (1 is used rather than 1-19). The command compares the number you're looking for to the table. In this case, we're looking for 75, so it compares the numbers in the first column to 75 until it finds one that is more than 75. Then it goes back up one row to get the desired value (.05). For this reason, the table needs to be set up in ascending order.

The vlookup command asks for "lookup value"-- that's the number of copies you say you made, "table array" -- the cells that have the price table (include all columns), and "column index num" that's which column has the prices, in this case it is the 2nd column in the table so you'd enter 2.

I hope that made sense!!


edited to add: the hlookup feature is the same but the table is set up horizontally.

edited to fix column name

Ok, I'm still confused. Bill I will check out the help thing, we're supposed to use the wizard for this exercise. Generally I don't like wizards.

Drice Thanks :)

LeapForJoy, I think that made sense, I'll know in a little bit ;)

I'm overfrustrated with this section because I normally pick up these things so quickly.

While I'm at it, averages with the MIN and MAX?

ps: a great reference book if you can find it
Mastering Excel by James Gips

My copy was written for Windows 95 but it is still a huge help with Office XP.

I used VLOOKUP quite a lot for work up until recently. You indicate the array, which is the group of values you will be searching. The function will look in the leftmost column for a matching value and will return the value in the same row of the coumn you indicate in the function. You need to make sure that your search column is sorted in ascending order.

Originally posted by HB Tigger Fan
While I'm at it, averages with the MIN and MAX?

I'm not sure what you mean. Can you give us an example?

I'll give the one from my book.

The test average is computed by dropping the students lowest grade, then giving equal weight to the three remaining tests. You will need to use the SUM and the MIN functions

So I'll give you the grades and everything

Test 1 ~ 60
Test 2 ~ 50
Test 3 ~ 40
Test 4 ~ 79

The range for the tests is B4:E4

We are supposed to get the formula and copy it for the remaining rows.

This could be the long way, but here's my first thought: sum the cells, subtract the minimum number, then divide by the count of cells minus 1.

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)

Or you could just divide by 3 if you knew it would always be this number of tests.

I understood the IF!!!!!!! YAY!!!!!!!!!!!!!!!!!!

Now I am working on the V and H lookups!

I always get vertical and horizontal mixed up. Vertical is up and down right?

Originally posted by HB Tigger Fan
I always get vertical and horizontal mixed up. Vertical is up and down right? Here's one way to remember: the horizon goes from left to right so horizontal is also left to right.

And Vertical is close to Vertigo which has to do with heights - up and down!

:D

-Demigod

WOOHOO! I figured out the IF and the VLookup & HLookup! Thank you!!!!!!!!!!!!!!!!!!!!!!

Now I can't figure out the IPMT and the PPMT. I figured out how to enter the formulas, where and what to enter, however I can't get it to calculate payments for the entire work sheet.

This is the info:

Principal ~ $150,000 (B3)
Annual Interest ~ 7.50% (B4)
Length of loan (in years) ~ 15 (F3)
Monthly Payment ~ $1,390.52 (F4) There has to be a formula in F4 to change the payment if anything else changes. This is the formula =PMT(B4/12,F3*12,-B3).

I have to figure out how much goes for principal and how much goes for interest.

So for the first cell in Interest I have =IPMT(B4/12,1,F3*12,-D$7,8.64) which results in $937.50

The first cell in Principal I have =PPMT($B$4/12,1,$F$3*12,-$D7) which results in $453.02

The results for IPMT and PPMT are correct for the first cells, however when I go to copy the formulas it doesn't work. There are 180 payments, and the end result of the balance should be $0.00.

I realize that my relative and absolute references probably aren't correct, however I am trying all of them and this is my latest attempt.

I have been using the Excel help and it's not helping. I have emailed my teacher but I haven't heard back from her yet and I want to figure this out.

Thanks!

Originally posted by HB Tigger Fan
So for the first cell in Interest I have =IPMT(B4/12,1,F3*12,-D$7,8.64) which results in $937.50

The first cell in Principal I have =PPMT($B$4/12,1,$F$3*12,-$D7) which results in $453.02

The results for IPMT and PPMT are correct for the first cells, however when I go to copy the formulas it doesn't work. You have several mistakes.

1. In your IPMT formula you have set the end value to be 8.64. It should be 0. (You can just leave that part out of the formula and it will assume 0.)

2. Your IPMT and PPMT formulas are referencing a D7 cell. What's in D7? Did you mean B3?

3. You need to adjust the formulas for each period you are calculating for. You can't just copy the formulas for period 1 into period 2 and so on, you have to adjust the period accordingly. So in period 2 the PPMT formula would be =PPMT($B$4/12,2,$F$3*12,-$B$3) all the way up to =PPMT($B$4/12,180,$F$3*12,-$B$3) for the 180th period.

4. You need to make all variables absolute. Notice in the above change I made the reference to B3 as $B$3. Make all the variables like that.

Thanks Bill! :)

This is the IPMT I used in the end =IPMT($B$4/12,A8,$F$3*12,-$B$3,0) and it gave me the answers I needed. We're supposed to make it so you can copy the formula by dragging.

This is the PPMT I used =PPMT($B$4/12,A8,$F$3*12,-$B$3)

Making the A8 (periods) not relative allowed it to be copied.

The last thing I am having trouble with, which seemed like the easiest thing is the running balance.

I was using =$D7-($B8+$C8), which is the starting balance - the interest + principal. I am not getting the correct answer in that cell let alone any other one. I have moved the ()s around and I am still not getting the answer.

OH! The D7 that I had before was the balance, formula being =$B3+FV($B$4/12,$F$3*12,$F$4,-$B$3).

Current (running) balance would just be the balance before the payment minus the principle only --- you can't subtract the interest from the balance because that's the "cost" of having the loan, you don't get credit for that part of the payment in your balance.

...Those first few years of payments on a mortgage are almost all interest -- it seems to take forever to be making a decent dent in the principle

Thanks Drice! That makes a lot of sense, and it worked!

YAY!

Yes, I am reviving this thread as it is not outdated.

I'm having issues with DSUM now. I figured out what my database is, but I can't figure out the other 2 steps (mainly the Criteria step).

Can anyone help?

The "field" indicates which database field or column is going to be used in the function.

The "criteria" is a range of cells that specify conditions for the function.

So, lets say my database is a range of cells containing data about people on MousePad. The database fields include name, age, height, gender, and hair color. Just for fun I want to know the combined age of all males over 5.5 feet tall.

I would first create some "criteria" cells that look like the database (have the same fields) but under the gender field I would have "male" and under height I would have ">5.5." Then in my DSUM function I would call the database cell range, the field ("age"), and the criteria cell range. The function will then only take the ages of all males over 5.5 feet tall and add them up.

In the database and criteria cell ranges be sure to include the cells for the headings of the columns (the names of the fields).

The numbers I need to add are in cells B8:B58. In B7 is the name "Electoral Votes". I have to add up the total electoral votes for Bush and Gore.

The 50 states are listed. Each winner is in colum C

These are the formulas I've tried:

=DSUM(B8:C58,"Winner","Gore") ~ Gives me VALUE!
=DSUM("Electoral_Votes","Winner","Gore") ~ Gives me an error
=DSUM("Electoral_Votes","B","Gore") ~ Gives me an error.
=DSUM("Electoral_Votes","B","Winner = Gore") gives me an error.

It's just not clicking. :(

You're putting values in the criteria instead of cell ranges. Put those criteria values in some cells (like E7:F8 if those are empty) and use that range in the criteria.

B7 says "Electoral Votes", I'm guessing C7 says "Winner"? Those cells have to be included in the range as well.

E7 will say "Electoral Votes" but E8 will be blank. F7 will say "Winner" and F8 will say "Gore."

The formula will then be:

=DSUM(B7:C58,"Electoral Votes",E7:F8)

You're trying to sum "Electoral Votes" so that's the value you want to send to the function, not "Winner."

Also, you don't have to have "Electoral Votes" in the criteria. In this case it's unnecessary.

So an alternate would be:

In E7 put Winner
In E8 put Gore
In E9 put =DSUM(B7:C58,"Electoral Votes",E7:E8)

Then for Bush:

In F7 put Winner
In F8 put Bush
In F9 put =DSUM(B7:C58,"Electoral Votes",F7:F8)

B5 is where the Gore total will go. B6 is where the Bush total will go.

B7 says Electoral Votes and the totals are in B8:B58

C7 says Winner and cells C8:C58 has either Bush or Gore listed (depending on who the winner is).

D7 says Gore & D8 says Bush but I don't think those colums are necessary in this formula.

I am supposed to add up the electoral votes for each person, so I think I need to use B7, B8:B58 and C7:C58

I'm confused about something else. Am I supposed to make a new worksheet with these values? When I had to do the assignment we used some sort of filter. I went back to look at the assignment and it just confused me more.

I really appriciate the help Bill!

I'm confused about something else. Am I supposed to make a new worksheet with these values? When I had to do the assignment we used some sort of filter. I went back to look at the assignment and it just confused me more.
You don't have to use a new worksheet. It really depends on what the teacher requires. Since I haven't seen the assignment than I don't know what the filter was for, so I can't help you there.

The answer is in post 23, but you'll have to adjust the cell locations yourself. If you can do that successfully than you understand how it works. You don't want me to do the work for you do you? ;)