Pelican Parts Forums

Pelican Parts Forums (http://forums.pelicanparts.com/)
-   Off Topic Discussions (http://forums.pelicanparts.com/off-topic-discussions/)
-   -   Need a little excel spreadsheet formula help please (http://forums.pelicanparts.com/off-topic-discussions/712794-need-little-excel-spreadsheet-formula-help-please.html)

lendaddy 10-22-2012 05:54 PM

Need a little excel spreadsheet formula help please
 
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 10-22-2012 06:00 PM

OK, try this.

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

BRPORSCHE 10-22-2012 06:10 PM

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

lendaddy 10-22-2012 06:16 PM

Think I have it:

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

lendaddy 10-22-2012 06:19 PM

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

Shuie 10-22-2012 07:19 PM

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 10-23-2012 03:27 AM

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

RWebb 10-23-2012 11:06 AM

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

BlueSkyJaunte 10-23-2012 01:52 PM

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))),S heet2!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))<AB70,AB70+1,IF ERROR(VLOOKUP(LEFT(H70,5),Sheet2!G:K,5,0),VLOOKUP( 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&"="&in dex!J14&";","")&IF(K14<>0,K14&"="&index!K14&";","" )&IF(L14<>0,L14&"="&index!L14&";","")&IF(M14<>0,M1 4&"="&index!M14&";","")&IF(N14<>0,N14&"="&index!N1 4&";","")&IF(O14<>0,O14&"="&index!O14&";","")&IF(P 14<>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,AF 14&"="&index!AF14&";","")&IF(AG14<>0,AG14&"="&inde x!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!AL1 4&";","")&IF(AM14<>0,AM14&"="&index!AM14&";","")&I F(AN14<>0,AN14&"="&index!AN14&";","")&IF(AO14<>0,A O14&"="&index!AO14&";","")&IF(AP14<>0,AP14&"="&ind ex!AP14&";","")&IF(AQ14<>0,AQ14&"="&index!AQ14&";" ,"")&IF(AR14<>0,AR14&"="&index!AR14&";","")&IF(AS1 4<>0,AS14&"="&index!AS14&";","")&IF(AT14<>0,AT14&" ="&index!AT14&";","")&IF(AU14<>0,AU14&"="&index!AU 14&";","")&IF(AV14<>0,AV14&"="&index!AV14&";","")& IF(AW14<>0,AW14&"="&index!AW14&";","")&IF(AX14<>0, AX14&"="&index!AX14&";","")&IF(AY14<>0,AY14&"="&in dex!AY14&";","")&IF(AZ14<>0,AZ14&"="&index!AZ14&"; ","")&IF(BA14<>0,BA14&"="&index!BA14&";","")&IF(BB 14<>0,BB14&"="&index!BB14&";","")&IF(BC14<>0,BC14& "="&index!BC14&";","")&IF(BD14<>0,BD14&"="&index!B D14&";","")&IF(BE14<>0,BE14&"="&index!BE14&";","") &IF(BF14<>0,BF14&"="&index!BF14&";","")&IF(BG14<>0 ,BG14&"="&index!BG14&";","")&IF(BH14<>0,BH14&"="&i ndex!BH14&";","")&IF(BI14<>0,BI14&"="&index!BI14&" ;","")&IF(BJ14<>0,BJ14&"="&index!BJ14&";","")&IF(B K14<>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,BU1 4&"="&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,BZ 14&"="&index!BZ14&";","")

jyl 10-23-2012 04:02 PM

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 10-23-2012 04:12 PM

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 10-23-2012 04:14 PM

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 10-23-2012 05:34 PM

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 10-23-2012 05:36 PM

Conditional formatting. In the menu.

Apply Conditional Format to Blank Cell - Microsoft Community

lendaddy 10-23-2012 05:43 PM

Quote:

Originally Posted by jyl (Post 7048787)

Ha, THX!


All times are GMT -8. The time now is 01:52 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2025, vBulletin Solutions, Inc.
Search Engine Optimization by vBSEO 3.6.0
Copyright 2025 Pelican Parts, LLC - Posts may be archived for display on the Pelican Parts Website


DTO Garage Plus vBulletin Plugins by Drive Thru Online, Inc.