Nested IF? Maybe AND? Both?

[slam]

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

 

[jproffer]

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>

 

[hotabae]

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:

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

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

=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:

[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)[>

 

[slam]

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?

 

[jproffer]

Maybe this:

=IF(OR(NOT(ISNUMBER(G3)),NOT(ISNUMBER(H3))),0,IF(G3<H3,"",IF(G3/2<G3-H3,G3/2,G3-H3)))

 

[slam]

<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>

 

[jproffer]

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?

 

[slam]

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.

 

[jproffer]

I'm pickin up what you're puttin' down now

How about this:

=IF(NOT(ISNUMBER(G3)),H3/2,IF(NOT(ISNUMBER(H3)),G3/2,IF(G3<H3,"",IF(G3/2<G3-H3,G3/2,G3-H3))))

 

[jtakw]

Hi,

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

Excel 2010
	G	H	I	J	K
3	14	6			7
4	6	10
5	8	6			2
6	8	this			4
7	that	14			7
Sheet26

Cell Formulas
Range	Formula
K3	=IF(OR(SUM(G3:H3)=G3,SUM(G3:H3)=H3),SUM(G3:H3)/2,IF(G3G3/2,G3-H3)))