Nested IF? Maybe AND? Both?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
848
Office Version
  1. 365
  2. 2019
Hi all,

I know this is a breeze for some of you, but I'm struggling a little.

I have values in G3 and H3. I'm trying to calculate the following

If G3 is < H3, make the cell appear blank with ""
else
If G3 / 2 is < G3 - H3
G3 / 2
else G3 - H3

i.e.
G3 H3
14 6
Result of formula would be 7 (14 / 2)

G3 H3
6 10
Result of formula would be ""

G3 H3
8 6
Result of formula would be 2 (8 - 6)


Hopefully I explained that correctly. Any help would be greatly appreciated. Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
This makes your examples come out right. Check it out and see what you think:

=IF(G3...is less than...H3,"",IF((G3/3)...is less than...(G3-H3),G3/2,G3-H3))

replace "is less than" with the correct symbol. This board doesn't like those symbols.....

<h3,"",if((g3 3)<(g3-h3),g3="" 2,g3-h3))[="" code]<h3,"",if((g3="" 2,g3-h3))<="" html=""></h3,"",if((g3>
 
Last edited:
Upvote 0
The Arguments for "=IF" are as follows:

IF(logical_test,[value_if_true],[value_if_false])

You can treat the [value_if_false] argument as your "ElseIf" case.

The first check of your loop (if it were a standalone) looks like this:

Code:
=If(G3 < H3,"", ELSEIF) <h3,"",elseif)[ code]

The nested portion of your loop (if it were a standalone) looks like this:

Code:
=If(G3/2 < G3 - H3, G3/2, G3-H3)

So, if you plop that^ right into the firstFormula, you'd end up with something like this:

Code:
[LEFT][COLOR=#222222][FONT=Tahoma]=If(G3 < H3,"", [COLOR=#222222][FONT=Tahoma]If(G3/2 < G3 - H3, G3/2, G3-H3)[/FONT][/COLOR]) [/FONT][/COLOR][/LEFT]
<h3,"",elseif)[ style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Tahoma,Calibri,Verdana,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;" code]
</h3,"",elseif)[><h3,"",if(g3 code]

I hope this helps!</h3,"",if(g3></h3,"",elseif)[>
 
Last edited:
Upvote 0
This makes your examples come out right. Check it out and see what you think:

=IF(G3...is less than...H3,"",IF((G3/3)...is less than...(G3-H3),G3/2,G3-H3))

replace "is less than" with the correct symbol. This board doesn't like those symbols.....

<h3,"",if((g3 3)<(g3-h3),g3="" 2,g3-h3))[="" code]<h3,"",if((g3="" 2,g3-h3))<="" html=""></h3,"",if((g3>

This works perfectly, but I see a small problem with my data which I didn't account for. I sometimes have text instead of a number in the columns, and this should be treated as a 0 (which I suppose means that the divide by two should always be invoked). I would I handle this?
 
Upvote 0
Maybe this:

PHP:
=IF(OR(NOT(ISNUMBER(G3)),NOT(ISNUMBER(H3))),0,IF(G3<H3,"",IF(G3/2<G3-H3,G3/2,G3-H3)))
 
Upvote 0
<h3,"",if(g3 2<g3-h3,g3="" 2,g3-h3)))[="" php][="" quote]
jproffer: Thanks, but unfortunately, that's giving me 0 for a result for instances where I have text.</h3,"",if(g3>
 
Upvote 0
I thought that's what you wanted. What do you want to happen if it's text? (I did see that you said "which I suppose means that the divide by two should always be invoked", but you can't divide a text by a number or vice-versa, so....what would you like the result to be if one cell or another is found to be non-numeric?
 
Upvote 0
I thought that's what you wanted. What do you want to happen if it's text? (I did see that you said "which I suppose means that the divide by two should always be invoked", but you can't divide a text by a number or vice-versa, so....what would you like the result to be if one cell or another is found to be non-numeric?

If I explained it badly, I apologize. Maybe I should have said if there's text, it should be ignored, and the other column with a numeric value should always be divided by 2. i.e.:


G3 H3
8 text
Result of formula would be 4

G3 H3
text 14
Result of formula would be 7

EDIT: there will never be an instance where both columns are text. One or both will always have a number.
 
Last edited:
Upvote 0
I'm pickin up what you're puttin' down now :)

How about this:

PHP:
=IF(NOT(ISNUMBER(G3)),H3/2,IF(NOT(ISNUMBER(H3)),G3/2,IF(G3<H3,"",IF(G3/2<G3-H3,G3/2,G3-H3))))
 
Upvote 0
Hi,

Hope I'm understanding your description, perhaps this will do what you want:


Excel 2010
GHIJK
31467
4610
5862
68this4
7that147
Sheet26
Cell Formulas
RangeFormula
K3=IF(OR(SUM(G3:H3)=G3,SUM(G3:H3)=H3),SUM(G3:H3)/2,IF(G3G3/2,G3-H3)))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top