Calculate the values using formula without hardcoding cells range

  • I want to calculate the values in my excel report which i am generating from sql developer. Currently the report is for previous one week but the report can also be generate for previous 3 days or 2 weeks also
    and also there can be many name,ids in the same sheet .
    So in this case i cannot the harcode the values in the cells range which i am doing currently.


    I have to create a button and now after clicking on the button it should create 3 new columns which is DIFF_LEOSOFT_CRM , DIFF_CRM_SALES, DIFF_SALES_ACCOUNTING..


    This columns will have to calculate difference between for example LEOSOFT to CRM, CRM to SALES, SALES to ACCOUNTING.
    The formula for the same is


    Code
    CRM - LEOSOFT/ LEOSOFT * 100


    I have set the minimum threshold to 3% and previously when i was hardcoding the cells value i used below formula which works fine. So If there is null value then it should set the color to green for the particular cells and if its greater than 3% then set the color to red:



    Below is the example how it should look like with the 3 new columns which is DIFF_LEOSOFT_CRM , DIFF_CRM_SALES, DIFF_SALES_ACCOUNTING
    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tCapture555.PNG Views:\t1 Size:\t107.2 KB ID:\t1195489","data-align":"none","data-attachmentid":"1195489","data-size":"full","title":"Capture555.PNG"}[/ATTACH]


    I have also attached the excel Report as an example for the same.


    REPORT.xlsx

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!