1

I have below data format in excel:

Previous

I would like to perform a gap analysis which must automatic show the 'gap' filled as below. enter image description here

I tried this formula: =RIGHT(J35,1)-RIGHT(K35,1)

But, I'm getting which says #VALUE!

EDIT: I'm trying it in this way.

    =IF(OR(RIGHT((J35,1),"None","Done"),RIGHT(J35,1)-RIGHT(K35,1),"-1","Done"))

Still problem

Is this required a VBA code? It seems, keeping output of if statement as formula in excel is not possible now.

Any other ways? How to achieve this ( the required details as shown in gap column)? Thanks.

Geert Bellekens
  • 12,788
  • 2
  • 23
  • 50
AskMe
  • 2,495
  • 8
  • 49
  • 102

1 Answers1

1

It seems like an incorrect use of both the IF and OR formulas. I believe you're trying to do it like this:

If the right character of J35 is a number, subtract the right characters of J35-K35 If J35 is "None", -1 If J35 is "Done, "Done"

This is a nested IF formula. The first formula is available in all versions of Excel, the second formula is available in newer versions.

=IF(ISNUMBER(VALUE(RIGHT(J35,1))),RIGHT(J35,1)-RIGHT(K35,1),IF(J35="None",-1,IF(J35="Done","Done")))

=IFS(ISNUMBER(VALUE(RIGHT(J35,1))),RIGHT(J35,1)-RIGHT(K35,1),J35="None",-1,J35="Done","Done")
kevin
  • 1,357
  • 1
  • 4
  • 10
  • What about: IF(OR(J35="DONE",K35="DONE),"DONE",IFERROR(RIGHT(J35,1)-RIGHT(K35,1),-1)) – Solar Mike May 06 '23 at 18:59
  • In case: the Previous is "None" and current is "A3" then the gap should be "-4" .. Any idea - how this should come? – AskMe Aug 27 '23 at 05:41