Excel Formula Help

[MBRGriffin]

Hello...I have 3 formulas that I am having trouble getting to do what I want that have been in use for quite some time. Any quick assistance would be very much appreciated!

1- I am trying to get the last populated cell value in a series of non consecutive cells in a row to return. The formula I wrote below works to return the single value (which is all they have been to this point), however, now I need it to accommodate a double digit number. (Note, there will actually be about 25 cells in each row for this formula to look through)

=if(iserr(mid((ad23&aj23&ap23),len(ad23&aj23&ap23),1)),"",mid((ad23&aj23&ap23),LEN(ad23,aj23,ap23),1))

2 - I have a date field that is returning the 1/0/1900 date, which I know how to adjust in the custom cell formatting to show as blank, but I also have a formula in these cells adding 14 days or subtracting 7 days, which then it is no longer considered '0'. how can I make this show as a blank when the date value is actually '0' but my formula is making it return with +14 (b15 is where my +14 is coming from) or -7?

=if($f19="Supplier",if(t19>1,t19-7,""),"") --returns 1/7/1900
=if($j19>0,if(n19="1",0,if(n19="4",0,if(o19>p19,0,max($p19+$b$15,$Q19,R19)))),0) - returns 1/14/1900

3 - I am counting the number of times in a column that 'NIS' is returned.... but I now have instances where the NIS I am clearing in conditional formatting if it is not applicable because it is an internal document but it is still being counted. what do I need to adjust to make this work?
My count if formula is this: =countif($x$19:$x$298,"NIS)
Here Is the formula in the x column: =if($k19=0,0,if($o19=0,NS",if($n19="1","APPROVED",if($n19="2"RESUBMIT",if($n19="3","RESUBMIT",if($n9="4",INFORMATION",""))))))
and my conditional formatting in x column says this: =$I19="Internal" format as white.

Any help is very much appreciated!!!

 

[MBRGriffin]

I've tried all of the below and they are returning errors/incorrect results. I am missing how to correctly replace the 42 with column af it seems .

=INDEX(AF19:FT19,MAX((MOD(COLUMN(AF19:FT19),6)=0)*(AF19:FT19<>"")*(COLUMN(AF19:FT19)-32))+1) -- returns 41939
=INDEX(AF19:FT19,MAX((MOD(COLUMN(AF19:FT19),6)=0)*(AF19:FT19<>"")*(COLUMN(AF19:FT19)-af))+1) -- returns #NAME?
=INDEX(AF19:FT19,MAX((MOD(COLUMN(AF19:FT19),6)=0)*(AF19:FT19<>"")*(COLUMN(AF19:FT19)-(af)))+1) -- returns #NAME?

 

[West Man]

=INDEX(AF19:FT19,MAX((MOD(COLUMN(AF19:FT19),6)=0)*(AF19:FT19<>"")*(COLUMN(AF19:FT19)-af))+1)

Change to: =INDEX(AF19:FT19,MAX((MOD(COLUMN(AF19:FT19),6)=2)*(AF19:FT19<>"")*(COLUMN(AF19:FT19)- COLUMN(af:af)))+1)

If the first Rev is in AF, to get all Revs, the remainder of the column # divided by 6 must be 2

 

[MBRGriffin]

That works!!! THANK YOU BOTH for your time and efforts! Verrry much appreciated!

 

[West Man]

You are welcome. Happy to help.

 

[MBRGriffin]

West Man - do you know how I can get no initial Rev itemized to return a blank instead of a zero by chance? Thanks!!

 

[West Man]

You have several options.
If you don't mind a zero in the cell as long as you cant see it, try a custom format of 0;-0;;@ for the cell. or you could even use conditional formatting and use the background color of the cell for the font color, rendering the 0 invisible.

If you must have an empty cell, the traditional way is IF(the formula=0,"",the formula)

or(untested) if(sum(MOD(COLUMN(AF19:FT19),6)=2)=0,"",INDEX(AF19:FT19,MAX((MOD(COLUMN(AF19:FT19),6)=2)*(AF19:FT19<>"")*(COLUMN(AF19:FT19)- COLUMN(af:af)))+1))

 

[MBRGriffin]

I do know of all of those options...unfortunately with these Rev columns, we start in alpha Rev's and then our first numeric used once it is approved by our clients is '0'...so 0 will be input and needed to return when entered. Is there any option you can think of that when all Rev cells are completely blank that it returns a blank instead of 0??

 

[West Man]

Not so sure about this but try: =IF(SUM(--(MOD(COLUMN(AF19:FT19),6)=2)*(AF19:FT19=""))=25,"",INDEX(AF19:FT19,MAX((MOD(COLUMN(AF19:FT19),6)=2)*(AF19:FT19<>"")*(COLUMN(AF19:FT19)- COLUMN(AF:AF)))+1))

 

[MBRGriffin]

That did it! Thanks so much! Last question, I promise! Aside from the column references, what else would I need to change in this formula if my Rev columns changed locations because of other added or deleted column (but were still always 6 columns apart)?

 

[West Man]

The only other change is the 2 in the mod. This number needs to be the remainder of the column # of the rev divided by 6.