• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trouble with Writing Correct Code

Jared P.

New Member
I am trying to find a specific string of code that would work for the following situation:

In simplest terms, I need a code that will produce a 0.00% percentage increase from 0.00% to 0.00%, but ALSO a 100.00% increase from 0.00% to any higher percentage (like 45.67%). I am trying to use IF, ISERROR, and OR commands but I cannot figure it out. I will attach a picture for reference.

0


An example of what I mean is the code I have written in cell C26 (=IF(ISERROR((C6-B6)/B6),1,(C6-B6)/B6)). This cell depends on the percentage increase from cell B6 to C6, but I have it programmed to produce a 100.00% if the statement is true. This is where I run into the problem. I want that cell to produce a 0.00%. At the same time, I want to be able to apply the exact same code to cell D27, which depends on the percentage increase from cell C6 to D6, which I want to be 100.00%. I do not want #DIV/0 errors (hence the IFERROR command).

Please help me if you can.

Thanks so much
 
Hi:

It would be more helpful if you can attach a sample work book and explain. BTW I am unable to see the snapshot you have attached.

Thanks
 
Thanks, Nebu. I will attach the workbook tomorrow, as it is saved at work. I will also try to go back and explain further on what I previously said. I will upload it at around 9:30 am.
 
I am trying to find a specific string of code that would work for the following situation:

In simplest terms, I need a code that will produce a 0.00% percentage increase from 0.00% to 0.00% (NOT a #DIV/0 Error), AND also a 100.00% increase from 0.00% to any higher percentage (like 21.28%). So far, I have only been able to formulate the second part of the needed code. I am trying to use IF, ISERROR, and OR commands but I cannot figure it out. I will attach a workbook for reference.

For example, cell C26 should represent the percentage increase from cell B6 (0.00%) to cell C6 (0.00%). I know that this would naturally give a #DIV/0 Error. I do not want that. I want it to come out as 0.00% instead. That individually was not hard to come up with.

That individual code would look like: =IF(ISERROR((C6-B6)/B6),0,(C6-B6)/B6)

The difficult part is configuring a separate part of the SAME string that would allow for a cell like D27, which represents the percentage increase from cell C6 (0.00%) to cell D6 (21.28%), to show 100.00% rather that another #DIV/0 Error. Individually, that code is easy to write as well.

That individual code would look like: =IF(ISERROR((D6-C6)/C6),1,(D6-C6)/C6)

I need ONE code for that would work for in any of the cells in array C26:E28.

Please help if you understand. I really don’t know how to explain further.

Thanks so much
 

Attachments

  • eXAMPLE WORKBOOK.xlsx
    12.2 KB · Views: 0
Dear Jared P

I believe the following formula exhibits your desired behaviour:

=IF(ISERROR((C6-B6)/B6),IF(C6=0,0,1),(C6-B6)/B6)
 
Dear Jared P

I believe the following formula exhibits your desired behaviour:

=IF(ISERROR((C6-B6)/B6),IF(C6=0,0,1),(C6-B6)/B6)

Jake,

Thank you so much. It worked great! I didn't even think about nesting another IF function.

These are why these forums are here. They're great.

I hope you have a great day!
 
Back
Top