Return Value Under Certain Conditions

[jdpro]

Good day all!

I would like to calculate values in column H, but if (E>F), then I need the calculation to be 0 or blank.

My current formula in column I is:
=IF(ISBLANK(G8),(D8*(A8*100))-(F8-E8)*A8*100)
It returns what I'm looking for, except when E>F
I manually entered the values in H to compare what I'm looking for vs what the formula gives me in I.

Your help is very much appreciated.

 

[jdpro]

Also I would really like to hide the FALSE but not sure how to do that. Thank you

 

[NdNoviceHlp]

Hi jdpro. Maybe....

=IF(E8<F8,IF(ISBLANK(G8),(D8*(A8*100))-(F8-E8)*A8*100),0)

HTH. Dave

 

[jdpro]

Hi NdNoviceHlp, and thank you!

I think we're halfway there. Here's the result. H is my desired outcome, I is my old formula, J is your formula. The bottom line is correct which is the most important thing.
It returns FALSE when the date is filled and E<F, and 0 when date is filled and E>F. Could I get a 0 instead of FALSE to make it more presentable?

 

[Peter_SSs]

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.

Try

=IF(AND(G2="",E2<=F2),(D2-F2+E2)*A2*100,0)

 

[NdNoviceHlp]

Perhaps...

=IF(E8<F8,IF(ISBLANK(G8),(D8*(A8*100))-(F8-E8)*A8*100,0),0)

Dave

 

[jdpro]

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.

Try

=IF(AND(G2="",E2<=F2),(D2-F2+E2)*A2*100,0)

Thank you much. Peter, this helped and is more condensed which is always nice.

I have tried to work with XL2bb but was not successful, will try it again. It would be much easier. I thought it might be because I am on a MAC that caused the issues I was having. But will look into it again. Always appreciate your help.

 

[Peter_SSs]

Thank you much. Peter, this helped and is more condensed which is always nice.

Cheers. It can actually be condensed a bit more

=AND(G2="",E2<=F2)*(D2-F2+E2)*A2*100

or

=(D2-F2+E2)*A2*100*(G2="")*(E2<=F2)

I have tried to work with XL2bb but was not successful, will try it again. It would be much easier. I thought it might be because I am on a MAC that caused the issues I was having.

MAC should be fine, provided you are using 365 per your profile.
Check the suggestions in this thread (though I am not sure if they are all relevant to MAC systems) otherwise tell us just what problems you run into and at what point of the instructions that occurs.