Formula Assistance Desperately Required

[Homer35805]

ALCON,

First thing I want to say is that I did not write this formula, but the person who did has departed to greener pastures without leaving any information at all on some of his, extremely complicated (well they look complicated) formulas that I now must maintain. Here is the formula

=IF(I63="Non-Leap Year",TEXT(VLOOKUP("Day "&TEXT(DAY(NOW()),"##"),'%'!A4:M34,MATCH(TEXT(NOW(),"MMM"),'%'!A3:M3,0),FALSE),"##.0%")&"  or  "&TEXT(B45*
(VLOOKUP("Day "&TEXT(DAY(NOW()),"##"),'%'!A4:N34,MATCH(TEXT(NOW(),"MMM"),'%'!A3:N3,0),FALSE))/1000000,"#,###,####.0")&"M",TEXT(VLOOKUP("Day "&
TEXT(DAY(NOW()),"##"),'%'!P4:AC34,MATCH(TEXT(NOW(),"MMM"),'%'!P3:AC3,0),FALSE),"##.0%")&"  or  "&TEXT(B45*(VLOOKUP("Day "&TEXT(DAY(NOW()),"##"),'%'!
P4:AC34,MATCH(TEXT(NOW(),"MMM"),'%'!P3:AC3,0),FALSE))/1000000,"#,###,####.0")&"M")

Yes, before you ask, one formula in one cell(currently resides in cell I64). The first thing I would like to do is figure out what he is/was trying to do here and can it be done easier? Secondly, can this formula be written in a more understandable way? Which I guess ties into the first question, but man, I saw this and my jaw dropped, literally....

So, any and all assistance is gratefully accepted.

Thank you to any who might respond, because at this moment, I am lost in the wilderness of this particular Excel formula.

[Bob Phillips]

It just looks up the day number and month name in one table or another depending upon if it is a leap year or not.

Can be re-written as

=TEXT(INDEX(IF(I63="Non-Leap Year",'%'!B4:M34,'%'!Q4:AC34),DAY(TODAY()),MONTH(TODAY())),"##.0%")
&" or "&TEXT(B45*INDEX(IF(I63="Non-Leap Year",'%'!B4:M34,'%'!Q4:AC34),DAY(TODAY()),MONTH(TODAY()))/1000000,"#,###,####.0")&"M"

[Homer35805]

Ok, thank you very much. However, when I tried the first section of code you supplied it came back with an answer of "67.1%", and the other formula had an answer of "92.3%", which is too much of a discrepancy for my boss to accept.

The part I could not figure out is the table it is using to lookup data. However, now I know, and who in their right mind would name a worksheet "%"?

That is just being lazy, because that % was throwing me off as I thought it was part of a formula, and since this is such a convoluted formula. Thank you though, your formula helped me with this one. Why he did it this way is beyond me....but I am hoping to rewrite it in a more functional way. Not sure why yours resulted in such a large discrepancy, but will run it through the formula analyzer to see where it went wrong.

ExcelTable.jpg

The above is a jpg representation of the tables on sheet "%". In case you wondered. Thanks again for the help, I do appreciate it.

[Bob Phillips]

Post your workbook, and I can check why they differ?

[snb]

I don't think you need a table to calculate the percentage:
This formula will do the trick

=(TODAY()-DATE(YEAR(TODAY())-N(MONTH(TODAY())<10);10;1))*0,3-(INT((TODAY()-DATE(YEAR(TODAY())-N(MONTH(TODAY())<10);10;2))/4)-2)*0,1