If Function error !!!

Solved/Closed
Report
-
 Waseem -
Hello,

I have created a time tracker worksheet for my process. Am just stuck up with one function.

I have used the below function which has 8 if statements which works very well -

"=IF(C2="","",IF(C2="STOCK DIVIDEND",($AD$4-E2),IF(C2="CASH DIVIDEND",($AD$4-E2),IF(C2="BOND INT",($AD$3-E2),IF(C2="ACQ AND MERG",($AD$3-E2),IF(C2="SPLIT & REV SPLIT",($AD$3-E2),IF(C2="CFD DIVIDEND",($AD$5-E2),IF(C2="Scrap 35",($AD$4-E2),"N/A"))))))))"

If i try to add another If criteria it displays an error. Is there anything like this which we can use If statement on 8 criteria.

Below is the fundction whichi i tried to use and got an error

"=IF(C2="","",IF(C2="STOCK DIVIDEND",($AD$4-E2),IF(C2="CASH DIVIDEND",($AD$4-E2),IF(C2="BOND INT",($AD$3-E2),IF(C2="ACQ AND MERG",($AD$3-E2),IF(C2="SPLIT & REV SPLIT",($AD$3-E2),IF(C2="CFD DIVIDEND",($AD$5-E2),IF(C2="Scrap 35",($AD$4-E2),IF(C2="Scrap 35",($AD$4-E2),"N/A")))))))))"

Could someone please help me out with the solution.

Thanks in Advance !!!

3 replies

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Hi Waseem,

Microsoft Excel's version before 2007, only 8 if commands are valid:

if you want to use use more if commands you can use micros or upgrade your Microsoft excel into 2007 or 2010.
Rizvisa,

i think you have replied something but i cant able to view !!!
Okay Rizvisa i will post and let you know !!!

Thank you very much !!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
This is what i said
https://ccm.net/forum/affich-589490-if-function-error#37


Here is your IF statement. The earlier one had some "," missing. That is one of the reasons, I would say that using UDF is better approach in this case

=IF(K2="","",IF(AND(K2>$AL$4,OR(C2="SCRAP 35",C2="QSRC",C2="REPORTIN",C2="SCRAP 32",C2=" EMAIL WATCH",C2="VICTORY FAIR VALUE",C2="DIVD PAY DATE REPORT",C2="SNAP FX RATE VERIFICATION MID DAY",C2="SCRUBBING AND VALIDATING DIVD")), "OUT OF SLA",IF(AND(K2>$AL$5,OR(C2="PRICE AT PAR",C2="COEFFICIENT FACTOR""ETRAN REPORT")), "OUT OF SLA",IF(AND(K2>$AL$3,OR(C2="MATRIX REPORTING",C2="EGYPT CANADA AND SWISS",C2="CACT",C2="PRICE AT PAR FOR HSBC",C2=" MULTIFOND MORINING REPORT",C2="CREATING AND SENDING DIVD VERIFICATION ",C2="PRIOR DAY PRICING REPORT",C2="SCRUB FX RATE VERIFICATION LONDON",C2="PM BLOOMBERG CACT CHECK")),"OUT OF SLA",K2))))
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Waseem,

Sorry for the wait, but here is your file with the second set of formula's broken down as requested:

https://authentification.site/files/28655310/tracker_template.xls

Best regards,
Trowa
Hi Trowa,

No probs for the wait !!! Thank you very much for great help !!!
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
7
Install Ms Office 2007 & Use Your Last Written Formula. It Will 101% Works.
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Hi Waseem,

Why don't you use Vlookup instead of Lots of If statements,

Just puts these values in a column say AD & AE :

CASH DIVIDEND $AB$4 -D2
STOCK DIVIDEND $AB$4 -D2
CFD DIVIDEND $AB$3 -D2
BOND INT $AB$5 -D2
and so on. now you can put as many as you required. (this is just an example not related to your formula)

and now put Formula in E2 as

= VLOOKUP(B2, $AD$1:$AD$100,2,false)