Excel 2007 : How to auto populate a field using an IF-TEHN statement

[JLJohnson19]

Hi,

I'm having difficulty with how to compose a formula. This is the dilemma:

I have drop down lists and a Total box. The total box represents a sum. From this, I want a field to populate with the name an element.

Let's say, the sum is in cell O40. I want to say, "Anything <4" will be populated in F174 with an element of "Continual Learning" but if the sum is >4, "I don't want anything" populated in F174.

How do I put this in a formula?

[Spencer101]

Could you post an example so we can fully understand your query?

[JLJohnson19]

I think I've been successful in attaching my spreadsheet.

From the total column in O40, I would like the cell in F174 to be populated with the term "Continual Learning" if the total in O40 is less than 4. If the total is more than 4, I would like nothing populated in that field.

Is this comprehensive enough. I'm really not very technically inclined in Excel and am doing the best I can to try and explain this.

Please advise.

[Spencer101]

Hi,

I don't appear to be able to open the attachment.

If I've understood correctly what you mean above, try entering the below formula in cell F174 and let me know if this is what you mean.

=IF(O40<4,"Continual Learning","")


Just as an additional question, what if the total in O40 is actually 4? Your explanation above only takes into account the total being below or above 4, not actually 4.

[JLJohnson19]

Spencer 101,

In my thinking, if 4 is entered in cell O40, nothing should be entered because there would be nothing to identify as a problem. But, when I entered the formula in cell F174, it enters the word "Continual Learning" automatically without me having entered the data in O40. I wanted it to be after O40 is populated with a figure of something below 4, "Continual Learning" would automatically populate after the spreadsheet calculates the data.

[Spencer101]

Apologies, complete oversight on my part.... Try.....

=IF(O40="","",IF(O40<4,"Continual Learning",""))

[JLJohnson19]

Slide1.JPG

Good Morning,

I've attached an image so you can see what I'm talking about. I hope you'll be able to open it. I've tried the formula and in the image you can see it gives a "False" statement although I've entered the appropriate parameters. Does this attachment help with my present scenario?

[Spencer101]

Try:

=IF(AND(O40>0,O40<4),"Continual Learning","")

[JLJohnson19]

Thank you but I think I'm doing something wrong, here and I can't for the life of me figure this out. I copied and pasted this formula into cell F174 and it still does nothing in connection with cell O40. Am I supposed to link these two cells or something? What am I doing wrong?

[Spencer101]

Works for me... See attached.

Check you have "auto calculate" turned on otherwise the formula will look like it's doing nothing until you press F9 or save the document.

[JLJohnson19]

YES!!! Finally..thank you for your patience and all of your help!

[Spencer101]

Not a problem. Glad it's sorted now

[JLJohnson19]

Last question...is there anyway we can delete the spreadsheet we shared from this post?

[Spencer101]

Yes. Go back and edit the relevant post. Select "Go Advanced" and "Manage Attachments".
Then at the bottom of the screen it will show the attachment with a small X in the top right corner.
Click that X and select done.

I've deleted the one from my post, but you'll have to do yours.

[JLJohnson19]

Thanks for all of your help. It's amazing!!!

[Spencer101]

Not a problem. Happy to help

Feel free to click on the little star below my post to give me some "reputation"