View Full Version : Excel - basic formula. Can't figure what I'm doing wrong.
DrJ
Jun 26, 2008, 12:23 PM
OK, so basically I have 2 percentages... I need one cell to display "Info B - Bad" if either both percentages are over 30% OR if either of them is over 50% (if the other was under 30%) - otherwise it would display "Info A - Good"
here is what I have:
=IF(E29>"0.30"&E33>"0.30",G33,IF(OR(E29>"0.50",E33>"0.50"),G33,G31))
E29 = 1st percentage
E33 = 2nd percentage
G31 = Info A - Good
G33 = Info B - Bad
no matter what the percentages say... it only displays Info B - Bad
This may not be the proper was to do formulas in Excel... but it's the only way I know how and I have made some pretty crazy long ones similar to this... but I seem to be missing something here... any ideas?
(EDIT: Btw no, this is not my homework :) this is my actual job)
KISS
Jun 26, 2008, 12:56 PM
I'm not going to solve the proble, but I'll give you two hints:
1. You need to loose the quotations.
2. To figure it out do it in pieces and then combine.
What I might do is explore conditional formatting. You can change the color of the box depending if the quantity is or is not in range.
DrJ
Jun 26, 2008, 01:42 PM
ahha... OK.. so I removed the quotes. Thought I needed them but I guess not.
then I broke it down into steps and discovered that the & didn't word here... I know that's usually for combining 2 cells but I thought it might work to combine 2 logical tests too. So I wrote out the And saw that excel wanted it set up similar to the OR statement (which I hadn't used until today either)
So that left me with this:
=IF(AND(E29>0.3,E33>0.3),G33,IF(OR(E29>0.5,E33>0.5),G33,G31))
and viola! She works!
Thanks KISS... you're a great teacher... Id rather get hints on how to figure it out than an answer that may not stick in my head any day!!
~DrJ~
westnlas
Jun 26, 2008, 02:06 PM
GREAT! I looked at it and figured a formula: IF((E29>0.30)*(E33>0.30)OR(E29>0.50)OR(E33>0.50),G33,G31). The quotes do signify text not percentages.
But, since I am self taught and only written spreadsheets for calculating the scores and home/away percentages for football & basketball games, it would be a guess at best.
I am glad you found the error. I have a sheet for the 332 NCAA men's basketball games that I simply cannot do. My computer crashes after about 2500 game scores are entered. I found an problem with the win percentages against the spread of the home teams last year and would like to track it better this next season.
I have learned in the last 2 years since I started learning how to turn on a computer, that the simplest tasks can create a loop in calculations that is almost impossible to find sometimes.
Good luck
DrJ
Jun 26, 2008, 02:37 PM
ahhhh.. sounds challenging! I recently began in Excel myself (well, its been a few years). I have created a pretty complex spreadsheet that we use to assess clients financial situation, debt amounts, interest, payoff, settlement amounts, fees, etc.. That all link up to a contract in Word... its been FUN! Being self-taught, too, I know I am doing a lot of it the hard way but it still works!
I ran into a loop a little while ago. There was actually a way to make excel only follow the loop X amount of times before it settled on a final answer for each variable. It was pretty accurate... at least close enough for what we needed it for.
Can't remember off the top of my head how I got there but it is a possibility. I believe (if you are using Excel 07) it was in the Excel Options when you click on the little menu icon in the top left.
JBeaucaire
Jun 26, 2008, 03:03 PM
I had fiddled with it and come up with the same stuff.
The only enhancement I'd suggest is NOT to hardcode the percentage into it. Instead, create a Benchmark Section:
J K
31 Benchmark 1 30%
32 Benchmark 2 50%
=IF(AND(E29>K31,E33>K31),G33,IF(OR(E29>K32,E33>K32),G33,G31))
Now you can change the lower and upper benchmarks anytime you want and your formula keeps working.
DrJ
Jun 26, 2008, 04:24 PM
o0o0o... I like the way you think, JB ;) Nice enhancement!!