How do I write an IF/THEN formula?

[Phlenin]

I use excel a lot but as far as formulas go, I've only dealt with them on a very very basic level. I need a complicated formula but I don't even really know where to look in the help section because I need to describe it. I have a cell on the sheet in which someone will enter a dollar amount. In another cell I need it to calculate a dollar value based on the number in the first cell. Here are the rules I need: If the entered dollar amount is less than $5000, the bonus dollar value should be zero. Then once it hits $5001 or higher, the bonus dollar value should be $500. In addition, for every whole $1000 above $5000 in the first cell, the bonus dollar value should increase by an additional $100.

So if I enter $5001 the formula cell should = $500
If I enter $7025 the formula cell should = $700 and so on

I can't simply do 10% since there is no bonus under $5001 and the additional $100's are only added when another whole $1000 is reached in the total.

I hope I'm explaining this in a way that makes sense and that someone can point me to how to accomplish this. Thank you in advance for your time.

[Aardigspook]

Welcome to the forum.

After the 5001 start, I'm not clear if you want the bonus to increase at 6000, 7000, 8000, etc or at 6001, 7001, 8001, etc? So here are two options:

This will give you 500 bonus for 5001-6000, 600 bonus for 6001-7000, etc:

Please Login or Register  to view this content.

If instead you want to have 600 bonus at 6000, not 6001, then use this:

Please Login or Register  to view this content.

Hope that helps.

[Phlenin]

Welcome to the forum.

After the 5001 start, I'm not clear if you want the bonus to increase at 6000, 7000, 8000, etc or at 6001, 7001, 8001, etc? So here are two options:

This will give you 500 bonus for 5001-6000, 600 bonus for 6001-7000, etc:

Please Login or Register  to view this content.

If instead you want to have 600 bonus at 6000, not 6001, then use this:

Please Login or Register  to view this content.

Hope that helps.

That second formula is EXACTLY what I needed! Thank you! You're a gem!

[Phlenin]

Ok I've got another one. I was trying to figure out how to convert the formula you gave me but I can't quite figure it. In this one, I need numbers 1-4 to = $0, 5 to = $300 and every number above 5 to add $50 more. So 6 = $350, 7 = $400, etc.

Also, is there a great place for me to learn this stuff on my own? I hate to be the annoying person who just asks questions but doesn't try to help themselves.

Thanks again!!

[Aardigspook]

This will do what you want:

Please Login or Register  to view this content.

In answer to your other question, there are lots of places you can learn from. The Excel help files are actually quite useful - search for different functions and try out the examples included. Have a look at some threads on this forum and try out the solutions.
It's a cliché but practice makes perfect - keep trying different things in Excel - if you want to do something, try to break it down into smaller steps.
For example, to come up with the formula above, your steps might be:

1. put the numbers 1-10 in A2:A11;
2. put the results you want in B2:B11;
3. see if you can spot a link between A and B:

a. 1-4 give a result of 0, so you can start the formula with IF( number is less than 5 , give answer of 0 → IF(A2<5,0
b. 5 gives 300, so maybe try multiplying by 60?
No, because that doesn't work with 6 (you want 350, not 360)
c. the increment is 50, so maybe you could add 50 to something?
So then 5 is 250+50, 6 is 300+50.
d. Yes, that works - 250 is 5 * 50, 300 is 6 * 50 ... hurrah!
e. Put it all together: If (A2 < 5 , amount is 0 , otherwise amount is (50 * A2) plus 50 )

4. Test the formula by putting it in another column and looking at the results it generates alongside the manually-typed results you want.

Hope that makes sense. Good luck!

[Aardigspook]

Thanks for the rep. If that means that you're happy that your question has been answered, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

[Phlenin]

Ok now don't laugh at me. I really am trying to understand how these formulas are written.

I have a cell that I want to calculate like this --- If C8 is $4500 or less, I want it to multiply by 9%, if it is greater than $4500, I want it to multiply by 13%.
I tried writing the formula this way and that's not working. I'm not understanding what the formula "says" if that makes sense. I understand from your last explanation (which was awesome by the way, but I'm a little slow with numbers), that a coma says "then" in the formula. I just don't understand what all the different parts of the formula "communicate" or indicate to be done, if that makes sense. Can you correct my formula below? Thank you again so so so much for your time. I truly appreciate it.

=IF(C8<=4500,*13%(C8*9%))

[JohnTopley]

Try


=IF(C8<=4500,C8*9%,C8*13%)

[FDibbins]

Thanks for the change

[MarvinP]

Hi Phlenin,

Your question seems like it needs a VLookup function with the 4th argument as TRUE. When this is True it looks down column 1 until it finds a value greater than the C8 value and you can then put your .09 or .13 in the second column. You will need to arrange your VLookup table from low to high in column 1 to do this.

http://www.excelfunctions.net/Vlooku...est-Match.html
or
https://www.excelcampus.com/function...st-match-true/

[Aardigspook]

Ok now don't laugh at me. I really am trying to understand how these formulas are written.

No-one here will laugh at you for not knowing something - we all started out that way.

I have a cell that I want to calculate like this --- If C8 is $4500 or less, I want it to multiply by 9%, if it is greater than $4500, I want it to multiply by 13%.

John Topley's formula in post 8 will do it.

I'm not understanding what the formula "says" if that makes sense. I understand from your last explanation ... that a coma says "then" in the formula. I just don't understand what all the different parts of the formula "communicate" or indicate to be done, if that makes sense.

All Excel formulae are built from different 'functions'. In each function, the commas separate different parts so that Excel can follow through logically. What each part between the commas 'means' to Excel varies depending on what the function is.
If you type a function name and a bracket into Excel, a tool-tip will pop up showing the different parts of the function, with the part you're 'in' shown in bold.
For example, let's take the IF function, since that's the one you're using at the moment.

1. In A1, put the number 1.
2. In B1, start typing: =IF(
The tool-tip will show: IF(logical_test, [value_if_true], [value_if_false])
So the part of the function you're 'in' is the 'logical test' - meaning 'Excel, check if this statement is true or false'.
3. Continue typing: =IF(A1=1,
The tool-tip will change to IF(logical_test, [value_if_true], [value_if_false])
So Excel now wants you to tell it what to give you if the check it's just done has come out true.
4. Continue typing: =IF(A1=1,"A1 is one"
To Excel this means: if the test 'A1=1' is 'True' then give the result "A1 is one" (the quotation marks surround something you want as text).
The tool-tip will still be the same, because you haven't yet typed a comma to leave the 'if true' section of the formula.
5. Type a comma further: =IF(A1=1,"A1 is one",
The tool-tip will now read: IF(logical_test, [value_if_true], [value_if_false])
Excel now wants you to tell it what to give you if the result of the check is False (that is, if A1 is not equal to 1).
6. Continue typing: =IF(A1=1,"A1 is one","A1 is another number"
You've now told Excel to give you the result 'A1 is another number' if A1 is not equal to 1.
The tool-tip stays the same as above, because you haven't yet finished the formula by closing it with a bracket.
7. Finish the formula with a bracket: =IF(A1=1,"A1 is one","A1 is another number")[/COLOR]
The tool-tip will disappear because you've finished the formula.

See the attached pic with the tool-tips shown.

You can nest different functions inside each other - or multiple instances of the same function, which is often done with IF.
For example:
=IF(A1=1, "A1 is one", IF(A1=2, "A1 is two", "A1 is another number" ))
This tells Excel to first check if A1=1. If it is, you get 'A1 is one'. If it's not, Excel will then check if A1=2. If that is true, you get 'A1 is two'. If it's not, you get 'A1 is another number'. Note that there are two brackets at the end of the formula, because you've got two functions.
Luckily, the tool-tip will change to show you where you are in each nested function (see second attached pic).

Regarding what commas 'mean', Excel will start at the left of a formula and move to the right, with each comma separating one part of the function from the next. For example, for DATE, the function is: DATE(year, month, day). In this, each part of the function doesn't depend on the others - the commas are there to remove any ambiguity. For example, the date 2017111 could be 1st Nov 2017 or 11th Jan 2017 - so the function is written to make it clear to Excel what you mean: DATE(2017,1,11) is clearly different from DATE(2017,11,1).

Another way to enter functions is to click on the little fx symbol to the left of the formula bar. Search for the function you think you need and you'll get a list of functions which include it. Select the closest one to what you want and click 'Go'. The window which pops up then gives guidance on what to put in each section of the function. See more attached pics.

The Excel help files are actually quite helpful in explaining individual functions. Alternatively, this site is a good one to get explanations of individual functions: http://www.excelfunctions.net (for example, IF is here: http://www.excelfunctions.net/ExcelIf.html; and DATE is here: http://www.excelfunctions.net/ExcelDateFunction.html).

Sorry if that's a bit long and perhaps over-simplified, but I hope it helps.