#### need help with formula #10

I'd appreciate help with a formula

I need a formula to calculate the following

4.5% of any amount up to and equal to \$41,100.00 PLUS 6.00% on an
amount above \$41,100.

ie \$20,000  would be (20,000 x .045) \$900.00

ie \$50,000 would be [(41,100 x .05) + (8,900 x .060)] \$2,383.50

Thanks Pete

--
pgruenin
-----------------------------------------------------------------------
pgruening's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2601

 0
10/24/2005 1:50:21 PM
excel.misc 78881 articles. 5 followers.

5 Replies
688 Views

Similar Articles

[PageSpeed] 33

I used
=IF(A1>41100,(A1-41100)*6%+(41100*4.5%),A1*4.5%)

Some notes and ideas: you can use cell references in place of the
41100, the 6%, and the 4.5% and enter those values in those cells,
rather than hardcode them.

 0
CycleZen (674)
10/24/2005 2:30:40 PM
Here's two ways:

=MIN(A1,41100)*0.045+MAX(0,A1-41100)*0.06

=A1*0.045+MAX(0,A1-41100)*0.015

--
Jim
"pgruening" <pgruening.1xetia_1130162708.9183@excelforum-nospam.com> wrote
in message news:pgruening.1xetia_1130162708.9183@excelforum-nospam.com...
|
| I'd appreciate help with a formula
|
| I need a formula to calculate the following
|
| 4.5% of any amount up to and equal to \$41,100.00 PLUS 6.00% on any
| amount above \$41,100.
|
| ie \$20,000  would be (20,000 x .045) \$900.00
|
| ie \$50,000 would be [(41,100 x .05) + (8,900 x .060)] \$2,383.50
|
| Thanks Peter
|
|
| --
| pgruening
| ------------------------------------------------------------------------
| pgruening's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26011
|

 0
jrrech (1932)
10/24/2005 2:31:49 PM
Assuming your Amount is in Column A:

=IF(A1>41100,(41100*.045)+(A1-41100)*.06, A1*.045)

"pgruening" wrote:

>
> I'd appreciate help with a formula
>
> I need a formula to calculate the following
>
> 4.5% of any amount up to and equal to \$41,100.00 PLUS 6.00% on any
> amount above \$41,100.
>
> ie \$20,000  would be (20,000 x .045) \$900.00
>
> ie \$50,000 would be [(41,100 x .05) + (8,900 x .060)] \$2,383.50
>
> Thanks Peter
>
>
> --
> pgruening
> ------------------------------------------------------------------------
> pgruening's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26011
>
>
 0
BekkiM (14)
10/24/2005 2:33:03 PM
Hi
Try something likes this assuming figure in a1
=if(a1>41100,(a1-41100)*.060+41100*.045,a1*.045)
Tina
"pgruening" wrote:

>
> I'd appreciate help with a formula
>
> I need a formula to calculate the following
>
> 4.5% of any amount up to and equal to \$41,100.00 PLUS 6.00% on any
> amount above \$41,100.
>
> ie \$20,000  would be (20,000 x .045) \$900.00
>
> ie \$50,000 would be [(41,100 x .05) + (8,900 x .060)] \$2,383.50
>
> Thanks Peter
>
>
> --
> pgruening
> ------------------------------------------------------------------------
> pgruening's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26011
>
>
 0
Tina (84)
10/24/2005 2:37:14 PM

Peter

--
pgruening
------------------------------------------------------------------------
pgruening's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26011

 0
10/24/2005 4:03:34 PM

Similar Artilces:

Help! File locked for editing.
We have a user (one out of about 7000) that is trying to run our Exce spreadsheet and when he opens it, he is getting a message that say File is locked for editing by..., then he opens it in Read only. W can't find the file open anywhere else and the attributes of the fil are not set to read-only. Is there anything else we can look for? It's as if the file is out there open somewhere on his machine, bu hidden. We also checked in Excel under the Window menu item to see i there are any hidden files, but none exist. Help! Another problem the user is getting is that when he closes the...

Applying formulas only to the subtotals of a data list
I have a large data list with subtotals (in fact with nested subtotals). I have collapsed the list so that only the subtotals are showing. Next I set up a few columns to the right of the data list with the intent of applying certain calculations, again ONLY to the subtotals. Here lieth the problem: when I set up the formulas for the first subtotal and attempt to simply drag it down, Excel obviously assumes that the copied formula applies to the next row of the full-blown, uncollapsed data list. Is there any way to accomplish the task without having to manually create a separate table ...

help DNS issue > cant send emails to aol domain
users are getting a bounce back that recipient could not be found in DNS. I'm only seeing this from aol domain and nothing else. I tried pinging aol.com and no reply and its telling me something isnt configured right on my DNS. Need some expert advise here ... thanks! Post the entire message. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "chris" <chris@discussions.microsoft.com> wrote in message news:5F3AF0CF-B037-403B-ABA3-9D6345026355@microsoft.com... > users are getting a bounce back that recipient could not be fou...

What tells Entourage it needs to rebuild database
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I came in this morning to have entourage tell me it wants to rebuild my database. This is not new. I have had this issue before therefore I have backups ready when this happens. <br> So I immediately replaced my main identity with the backup I have. When I restart entourage it still tells me it has to rebuild the database. If I try and rebuild the database, the database utility just freezes. I have many backups and every time I replace the Main Identity with a backup and start ent...

Macro with formulas...
Hello, I am trying to create a macro that records "ultra- repetative" keystrokes in a spreadsheet. I have to select a cell, enter the "=" sign in it, then select the cell directly above it. If I start in cell A2, then, at this point, it will read.. "=(A1" in cell A2... I need an absolute reference on the A1 cell reference in the formula contained in cell A2, so I type it in as such "=(\$A\$1" At this point, I know that I'm going to add cell A1 to another random cell in the spreadsheet... while the macro is still recording I finish typing what...

Send Error - Outlook 2002
I'm running Outlook 2002 on Windows XP. One of my e-mail accounts returns an error on send/receive. "Sending reported error (0x800CCC78): Unable to send the message. Please verify the email address in your account properties. The server responded Authentication Required. I realize this is due to my ISP's anti-spam gateway as I am at a location other than my assigned IP. However, I have nothing in my Outbox or Drafts folder. How can I get rid of this mystery email that Outlook is trying to send via this one email account? Any help greatly appreciated!! Thanks! julie ...

need macro to copy to 2nd worksheet
i hope this is possible, i need a little help. i use 2 excel files. the 1st file (i dont think this matters, but) the filename always changes InvoiceNumberTenantName.xls row 45 summarizes everything on the invoice to 1 row A45 to R45 i just copy the whole row then paste it to the 2nd file the 2nd files name does not change SummaryWithStatement.xls Sheet1 is where i paste (paste values) my row from the invoice file. this is the difficult part... this Sheet1 is a dynamic range so i would have to find the 1st empty row to paste. when i recorded the macro i was at A1, and scrolled down (i ...

Need reminder based on account balance V 9.0
I want to have a reminder (based on a smart list) come up when a GL account balance is not 0. The closest I can get is using period balance and have the reminder when the number of rows is greater than 0, but that triggers the reminder if period 1's balance is 10 and period 2's is -10. I need only when the current balance is 0. Does anyone know of a way to do this that I am missing? I don' believe there is a way using the standard SmartList. If you had SmartList Builder, you could create a new summary SmartList. -- Charles Allen, MVP "Bob Harrison" wrote: &...

A formula problem. #2
That worked perfect! Thank you! : -- parabol ----------------------------------------------------------------------- parabola's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1642 View this thread: http://www.excelforum.com/showthread.php?threadid=27794 ...

Help!
Im trying to filter a single entry out of a multiple entry field. In the properties section we have abreviations for the different investment properties we manage. Ex: Six, Stone, Mnt, TPL, Cyp, Wood, Vin, Vic, Ect. Im trying to run a filter that would give me each contact that had "Stone" in the properties section, but when I run the filter it only gives me the select few contacts that have only "Stone" in the properties section, not the others who have many entries including Stone. Can any one tell me the correct way to filter this. On Wed, 13 Feb 2008 14:34:04...

Control Box
I have a combo box whose list fill range is linked to a specific range. My issue is -that based on some other criteria - the list should be 5 or 10 numbers. How can I get the combobox to understand that this should be dynamic to the other variable ? ie - if program = A then the drop down list should be 1,2,3,4,5 {actually cells a1..a5} else the list should be 1,2,3,4,5,6,7,8,9,10 {cells a1..a10}? Thanks, yosef Hi ynissel Private Sub ComboBox1_GotFocus() If Range("d1").Value = "a" Then ComboBox1.ListFillRange = "a1:a5" Else: ComboBox1...

hola 05-10-10
This is a multi-part message in MIME format. ------=_NextPart_000_005C_01CAF03A.71E59080 Content-Type: multipart/alternative; boundary="----=_NextPart_001_005D_01CAF03A.71E59080" ------=_NextPart_001_005D_01CAF03A.71E59080 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable D=EDa luminosohola ------=_NextPart_001_005D_01CAF03A.71E59080 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"...

Help with Formula using Analysis ToolPack Function ISODD #2
Hi Peo, Thank you, but this workaround gives me the* total * number of Od numbers for the complete Dynamic Named Range ROOMS. =SUMPRODUCT(--(MOD(ROOMS,2)=1)) Is it possible to get the *count of Odd numbers per/ for eac individual Row of 9 Columns* using the Dynamic Named Range ROOMS. Thanks Tin� Peo Sjoblom Wrote: > One workaround > =SUMPRODUCT(--(MOD(ROOMS,2)=1)) > > Regards, > Peo Sjoblom > > "Tin�" <Tin.1eftrz@excelforum-nospam.com> wrote in message > news:Tin.1eftrz@excelforum-nospam.com...[color=blue] > > > > Hi, > > > ...

Need HELP
What IF function can I use so that Excel will look at a cell in a row if it is over 7,000, it should delete the row from the sheet, if it i less than 7000, it should multiply the value by .096?? your help will be greatly appreciated - thank you in advanc -- Message posted from http://www.ExcelForum.com There isn't one :-) Formulas cannot make changes that affect the structure of the workbook, they can only 'pull' data into the cell that the formula resides in, whether that be through a link or a calculation. VBA however can do this if you really wanted to go there. That h...

Ytd formula
I have a database in a workbook which is increasing on a daily basis. the column consiste of e.g Date country T.operators Rm Type Qty Amount etc I have a report separately which is link to the workbook by formulas and is controlled by a combo box when we changed the period or date. Is there a formula which will accumulated the year to date figures. Thks for your help -- Michel ...

Need help with SQL Statement in MS Access 2003 Chart
My Access Chart is not showing the data correctly. It's only counting line items by month per row and I am trying to get it to count each column as it's own data set. Chart SQL: SELECT (Format([Received Date],"MMM")), Count([Received Date]) AS [Qty Received], Count([DueBy Date]) AS [Has Due Date], Count([SentOn Date]) AS [Qty Sent] FROM [qryDeliveries] GROUP BY (Year([Received Date])*12 + Month([Received Date])-1),(Format([Received Date],"MMM")); Should Reflect: RCVD Has Due Sent Jan 16 11 8 Feb 11 10 9 Mar 23 19 16 Apr 12 13 6 May 1 ...

I have a spreadsheet that i exported from an old database program, in CSV format. I have it saved as a xls now, and my data is laid out like: lastname firstname dob date01 date 02 date 03 ticket01 ticket02 ticket03 (etc) lastname firstname dob date01 date 02 date 03 ticket01 ticket02 ticket03 I NEED (for importing into an access database) lastname firstname dob date01 ticket01 lastname firstname dob date02 ticket02 lastname firstname dob date03 ticket03 I have 2365 individual names... some with one date/ticket entry, some with 10.... HELP!? ...

Troubles Installing New Modules in GP 10.0 SP3
Hello: I've been having this problem for a month now without any success. The situation is like this. We have 4 companies, A, B, C, D. The real production companies are A and C. B and D are just test environments for the production companies A and C. Meaning that they're the same company with the same data, but for testing only. We have this companies with service pack 3, GP 10.0, 10.00.1193, installed in our production servers. The things is that initially the modules that were installed with GP were: Smartlist Generator, Extender, Debt Reclamation(I don't know the proper...

Help to create a 'trendline', I think..
I have a spreadsheet where your are supposed too archive curtain goals, based on different activities. I've got six different colums taht shows the activities, I wanna' have a line that shows how the activites mesures up to the goal. Ex. the goal (and the line) is to run 12 times a month, but you only run seven (activities) Anybody who could help, thanks Ns see my answer in microsoft.public.excel.worksheet.functions just for future reference it isn't necessary to post to more than one group .... all it does is fragment your answers and annoy people who may have spent tim...

HELP! which freaking patch(s) do I need for ENGLISH installation for the XP-SP2 install!
http://www.microsoft.com/downloads/details.aspx?FamilyID=95ed89d0-8b99-4458-b798-90ad5400923e&DisplayLang=en I've been to this page and I've tried the -ENG files, and they don't work (it says "data is invalid" on the client and "wrong language" on the server, what's the deal? which 2 files do I need? Thanks for any info you can provide! -Dave Vandenberghe dave_vandenberghe@yahoo.com Try the ENU files. ENG is for England. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 16 Sep 2004...

help! formulas #2
I have 2000 row shreadsheet of dates. Need to determine which date is greater than 10/31/2005 and return to another worksheet. Any guesses on least painful way to do? You haven't said whether there is more than one date > 10/31/2005, but you can try this array formula, entered with CTRL+SHIFT+ENTER: =MAX(IF(B2:B2001>DATE(2005,10,31),B2:B2001)) If there is more than one such date, the above returns the latest one. If you want the first one in the list, =INDEX(B2:B2001,MIN(IF(B2:B2001>DATE(2005,10,31),ROW(B2:B2001)-1))) On Fri, 23 Sep 2005 11:59:02 -0700, cindyd <cind...