What's wrong with this equation:

=IF(D45>8,(D45-D44),(D43=D45))

The first part, the true part, seems to work out, but when it turns out that D45 is less than 8, D43 never ends up equaling D45, it just becomes Zero.

Any help would be appreciated.

What cell is the formula in?

D43

doesn't inserting the formula in a cell referenced in the formula confuse excel?

I was going to say, you're essentially writing over your formula with the assignment, which I don't think you can do without confusing it.

If you want D43 to be equal to D45 if it is <=8, you can just type D45 after the second comma. Using D43 within a formula in cell D43 will give you a circular reference.

=IF(D45>8,(D45-D44),D45)

Is it possible that Excel is not treating D43=D45 as an assignment, but rather as a logical operator and returning a value of 0 for false?

It will usually return a zero when faced with a circular reference upon entering the formula for the first time. If the formula is pre-existing, and a value the driver reference changes to where the False scenerio is in play, then the previous value in the target cell (D43) will not change, but you will receive the circular reference error.

That did it. Thanks for the help. Any good Excel websites out there with useful tutorials?

Now, for some reason in the same formula this is Happening:

Cell D47....Formula: =SUM(B44:H44) ......is coming out as xero. B44 and C44 both have values of 3. The rest values of Zero.

The formulas in B44 and C44 are:

B44: =IF(B45>8,SUM(B45-8),SUM(D44=0))
C44: =IF(C45>8,SUM(C45-8),SUM(E44=0))

I've never really seen one. I've always learned via help. Some books do go into better detail though.

I've always just thought of If statements as If the criteria is made, then give me this, otherwise give me that (instead of make something something else). Never ask Excel to change the target cell, or an external cell directly within the formula itself.

That's pretty much how I parse it too, hence my logical operator assumption. I just opened up Excel and, sure enough, like you said, if I put a self-referential formula such as (=A1=A2) into a cell A1, it generates an error. (If anyone reading is confused, =A1=A2 means 'assign to this cell the result of A1=A2' 'A1=A2' is a logical (Boolean) test on whether A1 is equal to A2. If A1 does equal A2, TRUE is returned. Otherwise, FALSE is returned. )


However, if I put the number 1 in B1 and 2 in B2 and put the forumula (=B1=B2) in cell A1, this generates a value of "TRUE," no matter how my cell formatting is set. So I guess Excel never returns 0 or 1, but always FALSE and TRUE for such operators.

But, as far as I know, you can't make assignments from within a cell formula.

This thread totally gave me a mini-headache.

Yes, Excel will return True/False. This is evident, not only with the basic If statements, but essential in the SumIf and CountIf statements, as well as the IS statments (Isblank, Isna, etc.)

i was hoping to never see the word boolean again after graduating college

goddamn

sorry, didn't see the edit yesterday

the problem is in the last section of each formula. Excel is thinking you want to change the value in D44 and E44 to zero, but it won't do that. what you are asking it to do is if B45 is greater than 8, then populat B44 with the difference of B45-8. But if B45 is less than or equal to 8, it is not understanding your request. Do you want B44 to be zero?

This thread absolutely fucking rocks.

Math rock

There's a place I have bookmarked at work, where you can ask questions & programmers/power users will give you ideas/solutions...& see other peoples' issues & solutions. If I remember I'll post the link on Monday.

OK, so I didn't remember to post the links I had at work.

Here's a link to the Excel programming group @ Google groups.

Here's one for tricky Microsoft help

Meanwhile, I'm hoping someone can figure out what went on here today. We were trying to sort by a numerical column that once had text + numbers in it and now has just numbers, and was re-formatted as numbers. Not 'General,' but actual 2-decimal place numbers. About half the rows sorted correctly, but the remaining rows sorted among themselves, but not with the first 100 rows that sorted correctly the first time. If we re-typed the numerals in the errant cells of the sort column, those rows then sorted ok.

The error correction/numbers stored as text was active and no help. We did 2 different copy & paste special (values & formats; values) and neither of those worked. Any individual cell in the suspect range appeared to be formatted as a 2-decimal place number--just like the range that did sort correctly.

Does anyone know what happened here? We ended up retyping the faulty cells, and all the rows finally integrated. Thanks.

Another Excel question:

I need to get a cell to display a "+" sign -- i.e. "+21%"

I can't get it to treat it as normal text rather than a formula. any suggestions?

EDIT: Nevermind, found it.

Hell, I could have even answered that one...

Lowering the bar -- that's what I'm here for.

Usually you can highlight the whole column and ensure the format is what you want it to be. Instead of having to retype everything, you can use 'Text to Columns' under the Data menu (used to parse data in one column into more than one in most cases). It looks just like the import text wizard. It will default to delimited, but that won't matter. Don't make any changes and hit finish. Your data will be re-entered in the format you requested.

Back again.

So I have "formula A" and I want to multiply the result of "formula A" by "formula b" how do I do this?

Here's formula A:

=COUNTIF(SCHEDULE!C7:C18,A2)

So formula A determines the ammount of times the name listed in cell A2 appears between C7 and C18 on worksheet "schedule."

from the same worksheet "schedule" I want to multiply the difference between two different cells. I guess I'm confused how to do this.

If anyone knows what the hell I'm talking about, please set me straight.

thats exactly what it is doing