Forum Discussion
Return Positive Value if "Yes" Return Negative Value if "No"
I wrote this and put it in K39:
=IF(I39="","",IF(D39="Yes",I39*G39,IF(D39="No",-(I39*G39),"")))
I39 = Sale Price
D39 = Is the Invoice Paid (Yes/No)
G39 = Qty Sold
I input: I39 = $550.00, D39 = Yes or No (to test the formula), G39 = 1 (for testing).
I'm hoping that if there is no value for the sale entered then K39 (where the formula is input) will be blank. If the invoice has been paid then the sale price will be positive if not then the sale price will be negative.
Lower down I'm going to SUM K39 and then take .35% of that value for commission to the salesman. My problem is the formula isn't working and I've tried several things. Would love some help. Here's how it acts:
When D39 = No K39 returns "#VALUE!"
When D39 = Yes K39 returns $500.00
I figured out how to make the formula do what I'm wanting it to do. I was looking for it to return a value that wouldn't be calculable in the sum, in essence blank. So really the formula worked I was just looking at it wrong.
=IF(I39="","",IF(D39="Yes"",I39*G39,IF(D39="No",-(I39*G39),"")))
Yes: Returns value of I39*G39
No: Returns value of negative. But I didn't want it in the sum.
So I simply changed the formula to this
=IF(I73="","",IF(D73="Yes",I73*G73,IF(D73="No",0,"")))
Yes: Returns value of I39*G39
No: Returns $0.00 and if it's not Yes or No it's blank.
So this works. It also shows that there's a typo expecting it to either be a positive number or 0.
You can also replace the zero after the No with a "","" instead and get blank if it is No or something other than Yes. I think the 0 is better because it can't be included in the SUM and it shows that you have an invoice that hasn't been paid. It may not be the best formula but it works.
A big thank you to Sergei Baklan because I looked at his formula and then the light bulb came on and I realized that I didn't want it to express a negative (which indicated that it hadn't been paid, issue is that it calculates that negative in the SUM), instead I wanted it to not be added or subtracted from the SUM and therefore a $0.00 or a Blank would be best. So thanks Sergei... you turned the light on!
5 Replies
RepublicsFury I noticed that you reference I39 for the "Yes" and L39 for the "No". so is the I a capital i or a lowercase L? In other words should L39 be I39 instead?
mtarler It's a type. Should be I for the whole thing, sorry for the confusion .
I figured out how to make the formula do what I'm wanting it to do. I was looking for it to return a value that wouldn't be calculable in the sum, in essence blank. So really the formula worked I was just looking at it wrong.
=IF(I39="","",IF(D39="Yes"",I39*G39,IF(D39="No",-(I39*G39),"")))
Yes: Returns value of I39*G39
No: Returns value of negative. But I didn't want it in the sum.
So I simply changed the formula to this
=IF(I73="","",IF(D73="Yes",I73*G73,IF(D73="No",0,"")))
Yes: Returns value of I39*G39
No: Returns $0.00 and if it's not Yes or No it's blank.
So this works. It also shows that there's a typo expecting it to either be a positive number or 0.
You can also replace the zero after the No with a "","" instead and get blank if it is No or something other than Yes. I think the 0 is better because it can't be included in the SUM and it shows that you have an invoice that hasn't been paid. It may not be the best formula but it works.
A big thank you to Sergei Baklan because I looked at his formula and then the light bulb came on and I realized that I didn't want it to express a negative (which indicated that it hadn't been paid, issue is that it calculates that negative in the SUM), instead I wanted it to not be added or subtracted from the SUM and therefore a $0.00 or a Blank would be best. So thanks Sergei... you turned the light on!
In general formula works, I'm not sure about business logic (what it shall to calculate).
At least no errors. Better if you submit small sample file to illustrate an issue.
