none
Excel function/Code RRS feed

  • Question

  • Dear Friends,

    I am trying to solve a problem for my sheet but i don't know much about how to do it. I am new to it. Please advice.

    The sheet is given below:

     



    Expenses till date 62.00


    Date Day Expenses Detail Expense Cash in hand Cash at bank Salary Rd.
    1-Jan-12 Sunday Lunch 11.00
    5,000.00
    2-Jan-12 Monday Lunch 11.00
    5,000.00
    3-Jan-12 Tuesday Lunch & Milk 15.00
    5,000.00
    4-Jan-12 Wednesday Lunch 12.50 300.00 4,700.00
    5-Jan-12 Thursday Lunch 12.50
    4,700.00
    6-Jan-12 Friday


    4,700.00
    7-Jan-12 Saturday


    4,700.00
    8-Jan-12 Sunday Cash Withdrawl
    400.00 4,300.00
    9-Jan-12 Monday


    4,300.00
    10-Jan-12 Tuesday


    4,300.00
    11-Jan-12 Wednesday

    4,300.00
    12-Jan-12 Thursday


    4,300.00
    13-Jan-12 Friday


    4,300.00
    14-Jan-12 Saturday


    4,300.00
    15-Jan-12 Sunday


    4,300.00
    16-Jan-12 Monday


    4,300.00
    17-Jan-12 Tuesday


    4,300.00

    Is it possible that if the value in CASH in Hand cell is empty the corresponding cell value of Cash at bank also remain either blank or with "-" sign. Also the day on which I enter value in cell of column SALARY Rd., the Cash in Bank also get updated and display the value...how should i do to solve it.

    Please advice.

    Ashley

     

     

     

     

     

     

    Wednesday, January 11, 2012 5:46 PM

Answers

  • Hi Ashley,

    You could write a Function for it, but honestly, its a lot easier to just use Build-in Formula in Excel.

     

    So I would do something like this:

    In the Column 'Cash at Bank', I would calculate the values, like this to reduce the amount of 'Cash in Hand' and add the 'Salary Rcd':

    So the Formula will be: =F2-E3+H3

    See screenhot:

     

    Then you can hide the Column F 'Cash at Bank', to show only the Column 'New Cash at Bank', with a Formula like this: =IF(AND(E3="",H3=""),0,F3)

    See screenshot:

     

    Is this the result you were after?

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, January 13, 2012 6:56 PM
    Moderator

All replies

  • What is the equation you're using for the "Cash at bank" column? You could use an IF to see if the "Cash in hand" column is empty and act accordingly. Or you could do a conditional equation and use formual "F3=F2" and set that to blank.
    Wednesday, January 11, 2012 8:23 PM
  • Hi Ashley,

     

    As IMTheNachoMan already stated, you can make use of Formala's and formatting your Columns to accomplish this.

    I would create another Column and add this formula to it:

    First Row : =F2 (you want to have the start value)

    Then Second row and so on: =IF(E3="",0,F3)

     

    See screenshot formula:

     

    Then apply formatting for the Column, set to Accounting, see screenshot below:

     

    Can you explain this part a bit more:

    "Also the day on which I enter value in cell of column SALARY Rd., the Cash in Bank also get updated and display the value".

     

    Do you mean you want to enter an amount in that Column, and that will be deducted from Cash in Bank?

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, January 13, 2012 3:44 AM
    Moderator
  • Thank you for replying Daniel,

    Yes, I want the cash in bank to get updated once i receive the amount in salary received. Also if i enter value in the column "Cash in Hand" the amount should reduce in column "Cash at Bank".

     

    Please advice.

    Ashley

    Friday, January 13, 2012 5:09 AM
  • Thank you for replying.

    I am would try.

    Friday, January 13, 2012 5:10 AM
  • Hi Daniel,

    Is it not possible to keep only 1 column of "CASH at BANK". In this case we had to capture the last value >0 in the in the column which could be deducted from the left side (Cash at Bank). Here we could use some condition or a formula?

    Also another condition could be added in the above formula to verify the cell in the right side column (SALARY RD.), which could be added as well?

    Please advice.

    Friday, January 13, 2012 10:34 AM
  • Hi Daniel

    I feel that we to make some function of this type:

    Cash at Bank

    Formula / Function required in this order:

    For (i= current cell address)

    {

    If (left column cell value >0)

    {

    Above cell value – left column cell value + right column value

    Display value

    ElseIf(Above cell value ==0)

    {

    For (I = above cell value)

    {

    Above cell value—(address value should move to its above cell value…example E6 to E5)

    If(Above cell value >0)

    {

    Above cell value – left column cell value +right column value

    Display value

    }

    EndIf

    }

    }

    Can we make a function in this way to solve this issue?

    Please advice.

    Friday, January 13, 2012 11:04 AM
  • Hi Ashley,

    You could write a Function for it, but honestly, its a lot easier to just use Build-in Formula in Excel.

     

    So I would do something like this:

    In the Column 'Cash at Bank', I would calculate the values, like this to reduce the amount of 'Cash in Hand' and add the 'Salary Rcd':

    So the Formula will be: =F2-E3+H3

    See screenhot:

     

    Then you can hide the Column F 'Cash at Bank', to show only the Column 'New Cash at Bank', with a Formula like this: =IF(AND(E3="",H3=""),0,F3)

    See screenshot:

     

    Is this the result you were after?

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, January 13, 2012 6:56 PM
    Moderator
  • Hi Daniel,

    Thank you for guiding me. Yes, this would be better way of presentation and a lot easier. I was just getting curious about how to do the coding.

    I would follow your above steps.

    Thank you very much for your support and guidance.

    Have a great day!

    Ashley

    Friday, January 13, 2012 7:49 PM