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)

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.

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~

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

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.

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.

o0o0o... I like the way you think, JB ;) Nice enhancement!!