• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Help With Excel

Status
Not open for further replies.
J

JimW1949

Senior member
I am not certain this is the correct place to start this thread, but I think it is.

I am doing a friend a favor and I am trying to create an Excel spreadsheet with a formula. I used to know how to create all sorts of formulas with an older version of Microsoft Office, but I bought Office 2007 and it is different and to be quite honest, I kind of forgot how to do it.

I want to set it up so that it checks a certain to cell to see what the number in the cell is. If the number is less that 10, then I want to multiple the number in the cell by 2.5. If the number in that cell is between 11 and 20, then I want to multiple the cell number by 2. If the number in the cell is 21 or higher, I want to multiple the cell number by 1.5.

I would sure appreciate it if someone could help me with this.
 
=IF(A1<10,A1*2.5,IF(A1<20,A1*2,A1*1.5))

Or approximately something like that with options to add more robustness.
 
Imp said:
Covers what OP wants, except OP doesn't say what to do if it's 10...
Click to expand...

No, it doesn't. The OP wanted to do other stuff if it was between 11 and 20, what he posted would check between 10 and 20.

Basically < 10 Do this

From 12-19 Do This

> 20 Do that.

Doesn't say what to do with 11 or 10, or 20 so I assume nothing.
 
Numenorean said:
No, it doesn't. The OP wanted to do other stuff if it was between 11 and 20, what he posted would check between 10 and 20.

Basically < 10 Do this

From 12-19 Do This

> 20 Do that.

Doesn't say what to do with 11 or 10, or 20 so I assume nothing.
Click to expand...
edit: Oh, whatever. It's the right idea, he can change the numbers.
 
Last edited:
rcpratt said:
It's a nested IF...it handles 11-20 correctly.
Click to expand...

No it doesn't. It will multiply it by whatever. But according to the OP, they shouldn't be.

He said if the number is between 11 and 20, it should be multiplied by 2.

The IF statement there will multiply 11-19 by 2, when the OP indicates that it should be 12-19.
 
rcpratt said:
edit: Oh, whatever. It's the right idea, he can change the numbers.
Click to expand...

You need a completely different IF structure to handle the problem in the OP though.

I guess he could figure it out, but if he could do that then I figure he wouldn't be in here anyway.
 
Thanks guys, for all help.

I don't think this would work.

=IF(A1<10,A1*2.5,IF(A1<20,A1*2,A1*1.5))

If the number is less than 10 then it will multiply by 2.5, that is right. But then if the number less than 20 it will multiply by 2. That sounds OK, but 6 is less than 20 so how does it know which to multiply by?

I am sorry that I did not explain this in more detail. My wife wanted me to go to the grocery store for her. She has been fighting pneumonia and the flu for awhile now and she still isn't well. I don't feel too great either, she gave this crap to me, but I guess I am better off than she is right now. So I hurried up and typed out the post and then went to the store.

Anyway, basically what my friend wants is have a formula for his inventory. I don't know what actual dollar amounts he wants to set for the formula, I am just plugging in a number so I can figure out how to do the formula. Let's just say that when something costs him less than $10 (use $9.99), then he wants to charge 2.5 times his cost. If the item cost him between $10 and $19.99 then he wants to charge 2 times his cost. If the item is above $20 then he wants to charge 1.5 times his cost.

That's what he wants to do. I can figure out the less than $9.99 part and I can figure out the greater than $20 part, but the middle part, the between $10 and $19.99 part has me stumped.

Again guys, thanks for all your help. I really do appreciate it.
 
JimW1949 said:
Thanks guys, for all help.

I don't think this would work.

=IF(A1<10,A1*2.5,IF(A1<20,A1*2,A1*1.5))

If the number is less than 10 then it will multiply by 2.5, that is right. But then if the number less than 20 it will multiply by 2. That sounds OK, but 6 is less than 20 so how does it know which to multiply by?
Click to expand...
The IF with the <10 is before the <20 IF, it would never get to the 2nd IF.

Alternatively you could just do it and check that it does indeed work.
 
JimW1949 said:
=IF(A1<10,A1*2.5,IF(A1<20,A1*2,A1*1.5))

If the number is less than 10 then it will multiply by 2.5, that is right. But then if the number less than 20 it will multiply by 2. That sounds OK, but 6 is less than 20 so how does it know which to multiply by?
Click to expand...

Excel if syntax:

If(condition, value_if_true, value_if_false)

The check to see if A1<20 is only done if A1<10 is false.
 
I am legitimately not trying to be a douche, but if that lost you, your options are: (1) use it and trust that it works, or (2) find an Excel tutorial thing somewhere on the internet and read it.

edit: One more try. The 2nd IF statement is in the FALSE position of the first IF statement. The "code" only enters that second IF statement if the first is false. If it is true (<10), it never gets there.
 
Ok, I will give it a try. For some reason the formula just didn't seem right somehow. I am going to call my friend and get the exact numbers and punch them in. I probably won't get back here tonight but in any case I will let you know what happens tomorrow. Thanks again for all you help.
 
I called my friend and got the exact numbers he wanted to use and then I put those numbers into the formula instead of the ones we were using. You guys were absolutely right, it works perfectly. I can't thank you enough.
 
Status
Not open for further replies.

TRENDING THREADS

Back
Top