Microsoft Excel Forumla Help -- Anyone?

[herefordlovinglady]

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.

 

[stanglover2001]

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

 

[seedcorn]

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.

 

[herefordlovinglady]

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.

 

[FireTigeris]

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.

 

[herefordlovinglady]

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

 

[FireTigeris]

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...