Summing up until matching target figure

  • Hi,


    Assuming that we have a target number which is 15000 in Column C. Column B is blank and Column A has 1.


    The requirement is that when a number is entered in Column B, say 10000, if the number is less than Column C , which is 15000, is there a method that the number in Column A that is 1 is added to Column B until Column B matches Column A?


    Something like the if(B1<C1), B1+A1. However, this formula just adds 1 to B1 making it 10001 rather than keep adding until 15000 is reached.


    Does this require VBA? Could you please help?


    Regards,

  • Re: Summing up until matching target figure


    I don't understand what you mean - can you provide a sample workbook with your desired results?

  • Re: Summing up until matching target figure


    Hi,


    Sorry that I could not convey what I intended to. The requirement is like how you put a command in a program:-


    enter -A
    enter - B
    read


    if a< b
    a+1
    end if


    Display A
    Display B


    Though not perfect, this kind of command with "if- end if" will check the condition and keeps adding 1 to A until it equals to B. Can something be done in Excel? Sorry if this is a blunder.


    Regards

  • Re: Summing up until matching target figure


    What do you want to do with the result?


    Let's say you enter A as 10 and B as 20... If you wrote a loop to iterate, incrementing A until it reached B, what would you do in the loop?

  • Re: Summing up until matching target figure


    Hi,


    The requirement basically came up due to a backward % calculation difference in a trial salary calculation sheet.


    Salary in hand is calculated from Gross Salary by reducing and adding perks, some in percentage. When the gross salary is entered in its respective column, the salary in hand is to be calculated.


    I need an option where the salary in hand is entered and the Gross salary is backward calculated. However, the calculation is not coming perfect. I've tried iterating calculation to self calculate the output Gross Sal to match with the required Salary in Hand, but it cannot be fit in- the reason why the "if - end if" option was asked.


    I am attaching the excel sheet for your reference. The information are commented in the required cells and explanation is given in the spread sheet itself. Kindly peruse.


    Regards,

  • Re: Summing up until matching target figure


    I've had a look and I am struggling to wrap my head around what you're trying to do... If I am understanding correctly, you have a need to calculate what someone's gross salary needs to be in order for them to receive a specified take-home salary, complicated by the additions and deductions.


    However, I don't understand what all the various percentage values are on row 10... It seems you have created a complicated spreadsheet which has gone awry somewhere but we can't be sure where.


    It is overcomplicated by the fact that you have put in all the toggles - perhaps it would be better to have a separate worksheet for each scenario rather than all those nested IF statements...


    As it stands, I am afraid this is beyond me. :(

  • Re: Summing up until matching target figure


    Yes sir, you got it right about the purpose of the excel sheet. However, I will try and make a less complicated one and see if we could solve the issue. Will get back to you soon. Thank you for your time and patience. :)

  • Re: Summing up until matching target figure


    Sir,


    I am attaching an excel where the calculation is for any item purchased by a customer. Can a reverse calculation be made possible in this? I have included the remarks in the excel sheet. Could you kindly check?


    Regards,

  • Re: Summing up until matching target figure - Solved!



    Hi,


    I used the following VBA to do the calculation correction. This solved the problem, though took a bit of time. The Excel sheet is attached. It is protected to prevent accident modification. The VBA is used to calculate the take home salary calculation. Reverse calculation gives a variation in result. So the result is taken and matched with the TH input data and Gross Salary is increased/decreased as per the status and calculated until the output THS match the input. Cheers!


    VBA