Need a little excel spreadsheet formula help please

[lendaddy]

It's been a few years and I would have thought this wouldn't cause me any issues but alas, here I am.

I need to write a conditional formula. I currently have it as --- =IF(G3="X",$A4,0) and it works perfectly. But I need to add a couple more conditions to this now. I need to add the conditions of if G3="TP" then the cell must default to 0. Also if G3="TL" to default to zero and finally if G3="TD" default to 0. I'm sure I'm missing something simple but can't place it.

So ultimately if G3=X then assign a value of 1, but anything else or blank then 0.


Thanks in advance guys.

**edit: While typing this out I realized it works as I have it..well sorta. I'll clarify in a sec

[lendaddy]

OK, try this.

If G3="X' or "TP" then assign a value of 1 , otherwise 0

[BRPORSCHE]

Which version of excel? In mine there is a tab for conditional formatting. Let me play around and see what I can do.

[lendaddy]

Think I have it:

=IF(G3=L2,$E4, IF(G3="TP",$E4,0))

[lendaddy]

Yep, I'm good. Thanks anyway guys, sometimes talking it out lubricates the melon.

[Shuie]

You will need to dump it into a database and hit it with a SQL tool (decodes, etc) to do that.

Wait... you can you do that in Excel? Awesome!

[jyl]

You can use nested IF, or CASE, or lookup tables.

[RWebb]

we're a long way from VisiCalc, people...

[BlueSkyJaunte]

LOL, just on a whim I opened up one of my larger sheets that's sitting in my archives and found these:

=CEILING(IF(NOT(ISERROR(SEARCH("SL",Sheet2!S3))),Sheet2!M3*(Sheet2!U3/IF(Sheet2!K3="R",25,10))*Sheet2!AB3,Sheet2!M3),1)

=IF(IFERROR(VLOOKUP(LEFT(H70,5),Sheet2!G:K,5,0),VL OOKUP(LEFT(H70,3),Sheet2!A:K,11,0))

and

=D78&"&"&"IF("&A79&"2<>0,"&A79&"2&""|"","""")"

But this one takes the cake:

IF(E14<>0,E14&"="&index!E14&";","")&IF(F14<>0,F14&"="&index!F14&";","")&IF(G14<>0,G14&"="&index!G14&";","")&IF(H14<>0,H14&"="&index!H14&";","")&IF(I14<>0,I14&"="&index!I14&";","")&IF(J14<>0,J14&"="&index!J14&";","")&IF(K14<>0,K14&"="&index!K14&";","")&IF(L14<>0,L14&"="&index!L14&";","")&IF(M14<>0,M14&"="&index!M14&";","")&IF(N14<>0,N14&"="&index!N14&";","")&IF(O14<>0,O14&"="&index!O14&";","")&IF(P14<>0,P14&"="&index!P14&";","")&IF(Q14<>0,Q14&"="&index!Q14&";","")&IF(R14<>0,R14&"="&index!R14&";","")&IF(S14<>0,S14&"="&index!S14&";","")&IF(T14<>0,T14&"="&index!T14&";","")&IF(U14<>0,U14&"="&index!U14&";","")&IF(V14<>0,V14&"="&index!V14&";","")&IF(W14<>0,W14&"="&index!W14&";","")&IF(X14<>0,X14&"="&index!X14&";","")&IF(Y14<>0,Y14&"="&index!Y14&";","")&IF(Z14<>0,Z14&"="&index!Z14&";","")&IF(AA14<>0,AA14&"="&index!AA14&";","")&IF(AB14<>0,AB14&"="&index!AB14&";","")&IF(AC14<>0,AC14&"="&index!AC14&";","")&IF(AD14<>0,AD14&"="&index!AD14&";","")&IF(AE14<>0,AE14&"="&index!AE14&";","")&IF(AF14<>0,AF14&"="&index!AF14&";","")&IF(AG14<>0,AG14&"="&index!AG14&";","")&IF(AH14<>0,AH14&"="&index!AH14&";","")&IF(AI14<>0,AI14&"="&index!AI14&";","")&IF(AJ14<>0,AJ14&"="&index!AJ14&";","")&IF(AK14<>0,AK14&"="&index!AK14&";","")&IF(AL14<>0,AL14&"="&index!AL14&";","")&IF(AM14<>0,AM14&"="&index!AM14&";","")&IF(AN14<>0,AN14&"="&index!AN14&";","")&IF(AO14<>0,AO14&"="&index!AO14&";","")&IF(AP14<>0,AP14&"="&index!AP14&";","")&IF(AQ14<>0,AQ14&"="&index!AQ14&";","")&IF(AR14<>0,AR14&"="&index!AR14&";","")&IF(AS14<>0,AS14&"="&index!AS14&";","")&IF(AT14<>0,AT14&"="&index!AT14&";","")&IF(AU14<>0,AU14&"="&index!AU14&";","")&IF(AV14<>0,AV14&"="&index!AV14&";","")&IF(AW14<>0,AW14&"="&index!AW14&";","")&IF(AX14<>0,AX14&"="&index!AX14&";","")&IF(AY14<>0,AY14&"="&index!AY14&";","")&IF(AZ14<>0,AZ14&"="&index!AZ14&";","")&IF(BA14<>0,BA14&"="&index!BA14&";","")&IF(BB14<>0,BB14&"="&index!BB14&";","")&IF(BC14<>0,BC14&"="&index!BC14&";","")&IF(BD14<>0,BD14&"="&index!BD14&";","")&IF(BE14<>0,BE14&"="&index!BE14&";","")&IF(BF14<>0,BF14&"="&index!BF14&";","")&IF(BG14<>0,BG14&"="&index!BG14&";","")&IF(BH14<>0,BH14&"="&index!BH14&";","")&IF(BI14<>0,BI14&"="&index!BI14&";","")&IF(BJ14<>0,BJ14&"="&index!BJ14&";","")&IF(BK14<>0,BK14&"="&index!BK14&";","")&IF(BL14<>0,BL14&"="&index!BL14&";","")&IF(BM14<>0,BM14&"="&index!BM14&";","")&IF(BN14<>0,BN14&"="&index!BN14&";","")&IF(BO14<>0,BO14&"="&index!BO14&";","")&IF(BP14<>0,BP14&"="&index!BP14&";","")&IF(BQ14<>0,BQ14&"="&index!BQ14&";","")&IF(BR14<>0,BR14&"="&index!BR14&";","")&IF(BS14<>0,BS14&"="&index!BS14&";","")&IF(BT14<>0,BT14&"="&index!BT14&";","")&IF(BU14<>0,BU14&"="&index!BU14&";","")&IF(BV14<>0,BV14&"="&index!BV14&";","")&IF(BW14<>0,BW14&"="&index!BW14&";","")&IF(BX14<>0,BX14&"="&index!BX14&";","")&IF(BY14<>0,BY14&"="&index!BY14&";","")&IF(BZ14<>0,BZ14&"="&index!BZ14&";","")&IF(BW14<>0,BW14&"="&index!BW14&";","")&IF(BX14<>0,BX14&"="&index!BX14&";","")&IF(BY14<>0,BY14&"="&index!BY14&";","")&IF(BZ14<>0,BZ14&"="&index!BZ14&";","")

[jyl]

Nested IF is a bad idea if more than 2 or 3 deep. Way too confusing.

If can't learn CASE, build a lookup table somewhere.

[mjohnson]

I build the more complex logic equations up from a few columns of simple binary "if's". Trying it over a few dozen rows I can work it through in my head to feel confident that it works -- then apply to the big data set. Usually, especially if I'm doing it just for myself, I keep it piecemeal rather than being a hero and piling it into one huge formula. CPU cycles are pretty quick and cheap these days so most data can be done with brute force...

(of course if I'm giving the worksheet to somebody else I sex it up as much as possible!)

[mjohnson]

Grinding it in VBA is always an option, too. Could help speed things with huge data if you're not willing to wade into the whole locked cell/sheet and restricted autocalculation thing...

[lendaddy]

Is there a way to format cells so that if they are blank there is no shading but if there is any content at all then it would be shaded a predetermined way?

[jyl]

Conditional formatting. In the menu.

Apply Conditional Format to Blank Cell - Microsoft Community

[lendaddy]

Originally Posted by jyl

Conditional formatting. In the menu.

Apply Conditional Format to Blank Cell - Microsoft Community

Ha, THX!