=IF function with 2 true results and 3 false results

[doubtfire]

The IF function has 1 TRUE result and 1 FALSE result.
Can I have

Column A
1
2
3


'***This is pseudo code
Cells(A4).Value = "IF(SUM(A1:A3) >0, SUM(A1:A3) and Cells(1,5)="GREATER",0 and cells(1,5)="SMALLER")

The above sum is 6 and the expected result is A4=6 and E1="GREATER".
Is it possible ?
Thanks.:help

[PAB]

Hi doubtfire,

This will give the answer 6 in cell A4 and 0 if there is nothing in cells A1:A4.

Range("A4").Value = Evaluate("IF(SUM(A1:A3) >0, SUM(A1:A3),0)")

I do not understand quite what you are after though.
I hope this helps!

Regards,
PAB

[doubtfire]

Thanks.

[PAB]

Hi doubtfire,

Just worked out what you were after.
This should do it.

Range("A4").Value = Evaluate("=IF(AND(SUM(A1:A3)>0,E1=""GREATER""),SUM(A1:A3),0)")
Regards,
PAB

[PAB]

Hi doubtfire,

You could even use this.

Range("A4").Value = ["=IF(AND(SUM(A1:A3)>0,E1=""GREATER""),SUM(A1:A3),0)"]
Regards,
PAB

[snb]

??

=IF(SUM(A1:A3)>0;SUM(A1:A3) & " " & "greater";"0 smaller")

[doubtfire]

Guys ... thanks for all for the overwhelming response!!!
Actually, the project I am working is for the monthly sales report.

Range1 for the date
Cell1 for the "Bonus"/'Fine" (if the salesman does not meet a budget)
cell2 for the bonus$/fine $

That's why I am "greedy" to ask for under "IF=" ( just want to be dynamic when some changes are made to the Range1 for multiple positive/negative results.

I'm open to all advice/suggestions/ideas from all over the world.
:friends:

[snb]

What you want isn't clear.
Please post a sample workbook.

[doubtfire]

Thanks.
I try to explain like this and keep it simple

Cells(1,1).value = 1000
Cells(2,1).value = 500
Cells(3,1).formula = "=IF(SUM(A1:A2) > 2000, SUM(A1:A2), """")"

That's what I want,
Pseudo code
Cells(1,3).formula = "=IF(SUM(A1:A2) > 2000, SUM(A1:A2) _
AND Cells(1,10).Value = "BONUS" AND Cells(2,10).Value=SUM(A1:A2) * 0.1, _ """" AND Cells(1,10).Value = "FINE" AND Cells(2,10).Value=SUM(A1:A2) * -0.05)"

I understand we can have multiple conditions (AND/OR), but in regards to the results positive/negative can we have multiple too.
Thanks.:bow:

[PAB]

Hi doubtfire,

Perhaps if you could post a sample workbook like snb advised it will make what you want a bit clearer.

Regards,
PAB

[doubtfire]

I have attached a simple example to see the possibility Column F "=IF" formula can be embedded with formula in C9:C11. One place to do all rather to make other relationship somewhere else.

This is only an illustration to see whether "IF" function can have multiple positive/negative results. Design and efficiency should not be involved.

Thanks. :friends:

[PAB]

Hi doubtfire,

I am a little confused.
In your post you say that if the value earned is greater than 2,000 then multiply that amount by 0.10%, but in your Workbook you say multiply that amount by -0.10%.
I have gone for the former in my formula.
I assume that there will only be two options, Bonus or Fine.
I have taken it that they have to earn MORE than 2,000 in order to receive a BONUS.
Anyway, put this in cell C9 and copy down.


=IF(AND(SUM(C2:E2)>2000,B9="BONUS"),SUM(C2:E2)*0.1,SUM(2000-SUM(C2:E2))*-0.05)
Regards,
PAB

[PAB]

Hi doubtfire,

You could actually do it so you DON'T need to enter FINE or BONUS in any cells.
Put this formula in cell G2 and copy down.

=IF(SUM(C2:E2)>2000,"BONUS = " & SUM(C2:E2)*0.1,"FINE = " & SUM(2000-SUM(C2:E2))*-0.05)
Regards,
PAB

[doubtfire]

PAB,

First of all, thanks for the suggestion.

As I said, the design and efficiency is not involved.
The reason I put the result NOT on one place is because the second part with
the "BONUS"/"FINE" is linked to another function to put like the cheque # and amount for the salesman. The top is just the statistics.

In a word, the result is to be placed on two or three cells.
If the "=IF" can serve my purpose for more than a positive/negative result, I'll be more than happy to learn.

Thank you very much!:think:

[PAB]

I am obviously missing what you are trying to do then.
What is wrong with the formula ...

=If(And(SUM(C2:E2)>2000,B9="BONUS"),SUM(C2:E2)*0.1,SUM(2000-SUM(C2:E2))*-0.05)
What is missing?

Regards,
PAB

[doubtfire]

PAD,

Thank you again.
Your suggestion is great but just I'm greedy.

=If(And(SUM(C2:E2)>2000,B9="BONUS"),SUM(C2:E2)*0.1,SUM(2000-SUM(C2:E2))*-0.05)
You have "AND" for the condition, and I want "AND" for the positive/negative result. Whether "BONUS"/"FINE" depends (for CAD) on the result of cell "F2".
And since "BONUS" is created correspondingly the system will have cheque # and amount issued and recorded.

I apologize not being clear.
Thanks.:yes

[PAB]

Something like.

=IF(AND(SUM(C2:E2)>2000,B9="BONUS"),SUM(C2:E2)*0.1,IF(AND(SUM(C2:E2)<=2000,B9="FINE"),2000-SUM(C2:E2))*-0.05)
You have the "IF(AND(" for BOTH the positive and negative.
Is this RIGHT?

Regards,
PAB

[PAB]

Hi doubtfire,

Have NONE of my solutions given you the RIGHT answer?

Regards,
PAB

[doubtfire]

PAB,

Thank you again.

Your answer always give me a chance to learn and new ideas, it or they might not be the answers I expect but it or they direct me to new exploration in some other ways. In a word it is beneficiary.

=If(And(SUM(C2:E2)>2000,B9="BONUS"),SUM(C2:E2)*0.1,If(And(SUM(C2:E2)<=2000,B9="FINE"),2000-SUM(C2:E2))*-0.05)
I have a question in regards to the "IF" function,

IF(condition, True, False)

in your reply, does B9 need to have something like "BONUS"/"FINE" in order the quotes to be true !?

My expectation is B9 is filled based on the outcome of the SUM above.

If I misread your response please guide me.

Thank you again.
Faithfully yours.:bow:

[PAB]

Hi doubtfire,


in your reply, does B9 need to have something like "BONUS"/"FINE" in order the quotes to be true !?
The answer is YES, there needs to be the text "BONUS" or "FINE".

Are you saying that you would like either the "BONUS" or "FINE" put in B9 etc through a formula?

Regards,
PAB

[PAB]

Hi doubtfire,

If that is the case then put this formula in cell B9 and copy down.


=IF(SUM(C2:E2)>2000,"BONUS","FINE")
Regards,
PAB

[Paul_Hossler]

I'd use a user defined function, esp since you already have macros

This is an array formual, so select B9:C9, enter the formula, and Control+Shift+Enter and Excel will add the array braces



Function BonusOrFine(MonthlySales As Range, Optional Threshold As Double = 2000) As Variant
Dim v(1 To 2) As Variant
Dim QuarterTotal As Double

QuarterTotal = MonthlySales.Cells(1, 1) + MonthlySales.Cells(1, 2) + MonthlySales.Cells(1, 3)

If QuarterTotal < Threshold Then

v(1) = "FINE"
v(2) = -0.05 * (Threshold - QuarterTotal)

Else

v(1) = "BONUS"
v(2) = 0.1 * QuarterTotal
End If

BonusOrFine = v
End Function


Paul

[doubtfire]

PAB,

Yes. I want the original "=IF(" either True or False to put the result on the cell itself AND to place on other cells too like "BONUS"/"FINE".
Thanks.

[snb]

You can't use a formula to change more than 1 cell values.
Please read your Excel book on formulae.

[doubtfire]

:yes
Thanks for the colorful "Smarties"!
If I'm using VBA, can I run a sub/function within the "=IF(..." in order to update other cells or perform other actions?
Thank you ALL!!!:beerchug:

[Paul_Hossler]

If I'm using VBA, can I run a sub/function within the "=IF(..." in order to update other cells or perform other actions?


No, unless you use the Worksheet_Change event, which is a seperate sub, and not in the WS formula




You can't use a formula to change more than 1 cell values.


A WS formula cannot change font, color, etc. on a worksheet, not can it change the values of other cells. It returns a value

However, an Array formula can change the values in more than one cell, but again only the values

That's why I used a User Defined Function in my example to return a 1R2C array

Paul

[doubtfire]

Thank you again to all.:beerchug: