Microsoft Excel Forumla Help -- Anyone?

Discussion in 'Random Ramblings' started by herefordlovinglady, Feb 2, 2011.

  1. herefordlovinglady

    herefordlovinglady It Is What It Is

    Jun 23, 2009
    Georgia
    I know enough in excel to be dangerous. I am setting up a spreadsheet and need to calculate a total based on the following:

    1 to 10 days = 1%
    11 to 20 days = 2%
    21 to 30 days = 3%
    31 to 40 days = 4%
    41 to 50 days = 5%
    51 to 60 days = 6%
    61 to 70 days = 7%
    71 to 80 days = 8%
    81 to 90 days = 9%

    So if we are paid on the 12th day from an entered date, I need to calculate 2% of the total payment, if we are paid on the 32nd day from the same entered date I will need the spreadsheet to calculate 4% and enter the amount

    I know there is an IF formula that would work


    The invoice is 1,000 submitted on 2/1/11:


    if we are paid within 10 days then the difference would be 10.00
    if we are paid within 30 days the difference would be 30.00, etc.

    but i do not want to have to manually calculate the days, i want the spreadsheet to enter the number of days and then the percentage dollar amount can be calculated with another formula.
     
  2. stanglover2001

    stanglover2001 Chillin' With My Peeps

    1,630
    12
    161
    Apr 29, 2010
    I'll be able to help you by April or May, when I pass my college class. I'm in the 3rd-4th totorial of my Excel class, we're now just learning the IF function, later in march or april will be learninig the nested IF functions. I hope someone else can help ya, I'm not there yet [​IMG]
     
  3. seedcorn

    seedcorn Chillin' With My Peeps

    Apr 25, 2007
    NE. IN
    I'm stupid, I really don't understand what you want. I work w/excel all the time, so I should be able to help you make a formula. I think what you want is:

    Pick a cell (use A1) enter the amount. Make B1 .01; C1 .02, etc until you get all the %'s you want.

    Cell B2 formula would be =$A$1*B1; Copy it, paste it to the other cells B2, etc. All you have to do now is enter the amount into A1 and it should adjust on it's own.

    If you want, you can use first row to place descriptions of what they are, then you'd use B or C row. You may need to highlight all the cells and format the cells into currency. May also want to format the columns for width. IF you want the entire amount to show, use 1.01 instead of .01, etc.

    If this isn't what you want, I'll delete this post.
     
    Last edited: Feb 2, 2011
  4. herefordlovinglady

    herefordlovinglady It Is What It Is

    Jun 23, 2009
    Georgia
    by george i think i got it --

    =IF(J5<=10,"1%", IF(J5<=20,"2%", and so on.



    well i haven't tested it, but i think i am on the right track.

    thanks for the responses.
     
  5. FireTigeris

    FireTigeris Tyger! Tyger! burning bright

    Column owed at store / Column # of Days / Column % owed / Column total owed

    ------------------------------------------------------------------------------------------------------
    (lets say those are the columns, row 1 columns A-D)
    ------------------------------------------------------------------------------------------------------

    A2-AXX/ B2-BXX / will be numeric inputs, C2-CXX and D2-DXX will be figured with formulas.

    --------------------------------------------------------------------------------------------------------

    In row 2: written formula for C2 = when input number of day then output into C2 based on every 10 days +1%.

    hummm-

    If b2 (=,<) 10, C2=1; else B2/10 = C2

    that might work... then force C2 to be a whole number.
     
    Last edited: Feb 2, 2011
  6. herefordlovinglady

    herefordlovinglady It Is What It Is

    Jun 23, 2009
    Georgia
    Quote:I will try this, i got an error saying current formula could not be used because it uses more levels of nesting than allowed in current file format.

    I told you i was dangerous
     
  7. FireTigeris

    FireTigeris Tyger! Tyger! burning bright

    Quote:I will try this, i got an error saying current formula could not be used because it uses more levels of nesting than allowed in current file format.

    I told you i was dangerous

    I was trying on paper what you were trying one post above, in the edit for the column there is a field that allows how many place values set it to allow only whole numbers.

    ok, if it needs it simpler pick column YY or ZZ do this.


    if YY2 = b2 (<) 10 add 10, else yy2=b2

    C2 = yy2/10 = this would be the number for the percent value, not a percent... 1,2,3,4,5...

    d2= A2*(1.01*c2) or whatever you are doing...
     
    Last edited: Feb 2, 2011

BackYard Chickens is proudly sponsored by