IF formula that shows "FALSE" instead of prepared text, and a few bugs

Hi,

I've created a formula that checks to see if there is data in certain cells, if all the cells don't have something in them, the user will get an error "You do not qualify for this expense"
1. Looks at cell BE3, to make sure it is NOT blank
2. Looks at cell BG3, to make sure it is NOT blank
3. Looks at cell BI3, to see if there is data in it and to see if user selected "US" or "Canada"
4. Looks at cell BR3, to see if there is data in it and to see if to see if the user selected "Yes" or No"

If the user has data in BE3 & BG3 and selects "US" in BI3 and "Yes" in BR3, they will get a message "You qualify for $25 per month"
If the user has data in BE3 & BG3 and selects "Canada" in BI3 and "Yes" in BR3, they will get a message "You qualify for $30 per month"

If any of the cells are empty, Or if they select "No" in cell BR4, they should get the message: "You do not qualify for this expense"
I have created the formula:

=IF(AND(BE3<>""),IF(OR(BG3<>""),IF(AND(BI3="us",BR3="yes"),"Up to $25 per month",IF(OR(BI3="canada",BR3="yes"),"Up to $30 per month"))),"You do not qualify for this expense")

I am getting the formula to work ALMOST correctly, when this happens:
If the user has data in BE3 & BG3 and selects "US" in BI3 and "Yes" in BR3, they will get a message "You qualify for $25 per month" - PERFECT!!
If the user has data in BE3 & BG3 and selects "Canada" in BI3 and "Yes" in BR3, they will get a message "You qualify for $30 per month" - PERFECT!!

But if...
If the user has data in BE3 & BG3 and selects "US" in BI3 and "No" in BR3, I get a message: "FALSE" - NOT PERFECT, but liveable!!

Or if...
If the user has data in BE3 & BG3 and selects "Canada" in BI3 and "No" in BR3, I get a message: "You qualify for $30 per month" - WRONG!
If there is no data in BG3: "FALSE" - NOT PERFECT, but liveable!!
If there is no data in BE3: "You do not qualify for this expense" - I want this message to show up each time, one of the boxes is blank, or "No" is selected

I know I have just something miswired... can anyone please help? I have been trying variations of this all day...

Hi there!

Thanks for trying, but it didn't quite work.
I have created a sample file, and highlighted the fields I am referring to, since they are no longer: BE3, BG3, BI3 or BR3.

Sample Audit Steps for Excel Forum 6.27.2012.xlsx

I hope I attached this correctly.

Your help is so greatly appreciated! I entered your formula into the first row, and you can see what it is doing.

Annie

Maybe

Formula:

=IF(OR(BI3="",BR3=""),"",IF(OR(BR3="No",BE3="",BG3=""),"You do not qualify for this expense","You qualify for "&IF(BI3="Canada","$30","$25")&" per month"))

Hi again,

I tweaked the formula FDibbins created for me to:
=IF(OR(BR3="No",OR(BE3="",BG3="")),"You do not qualify for this expense",IF(AND(BI3="us",BR3="yes"),"Up to $25 per month",IF(AND(BI3="canada",BR3="yes"),"Up to $30 per month","You do not qualify for this expense")))

And now it works perfectly!!

Thank you guys so much!!!
Annie