calculating a minimum fee

I'm looking for a formula that will calculate the following information:

If 15% of the sum of a range of cells is less than $40.00 then the fee is 
$40.00.
If 15% of the sum of a range of cells is $40.00 or higher, then the fee is 
15%.

I hope that made sense.

In other words, the fee is 15% or $40, whichever is higher. 
0
Utf
1/8/2010 8:57:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
632 Views

Similar Articles

[PageSpeed] 3

Hi

Look at this:

=MAX(A1*0.15,40)

Regards,
Per

"Jackie" <Jackie@discussions.microsoft.com> skrev i meddelelsen 
news:5B6BA4D3-CBBE-4C8A-A7AF-A2409C6799F2@microsoft.com...
> I'm looking for a formula that will calculate the following information:
>
> If 15% of the sum of a range of cells is less than $40.00 then the fee is
> $40.00.
> If 15% of the sum of a range of cells is $40.00 or higher, then the fee is
> 15%.
>
> I hope that made sense.
>
> In other words, the fee is 15% or $40, whichever is higher. 

0
Per
1/8/2010 9:10:28 PM
=MAX(40,15%*SUM(A1:A9))

Adjust the range (A1:A9) to your needs

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jackie" <Jackie@discussions.microsoft.com> wrote in message 
news:5B6BA4D3-CBBE-4C8A-A7AF-A2409C6799F2@microsoft.com...
> I'm looking for a formula that will calculate the following information:
>
> If 15% of the sum of a range of cells is less than $40.00 then the fee is
> $40.00.
> If 15% of the sum of a range of cells is $40.00 or higher, then the fee is
> 15%.
>
> I hope that made sense.
>
> In other words, the fee is 15% or $40, whichever is higher. 

0
Niek
1/8/2010 9:13:55 PM
YOU ARE FABULOUS!  THANK YOU SO MUCH.



"Niek Otten" wrote:

> =MAX(40,15%*SUM(A1:A9))
> 
> Adjust the range (A1:A9) to your needs
> 
> -- 
> Kind regards,
> 
> Niek Otten
> Microsoft MVP - Excel
> 
> "Jackie" <Jackie@discussions.microsoft.com> wrote in message 
> news:5B6BA4D3-CBBE-4C8A-A7AF-A2409C6799F2@microsoft.com...
> > I'm looking for a formula that will calculate the following information:
> >
> > If 15% of the sum of a range of cells is less than $40.00 then the fee is
> > $40.00.
> > If 15% of the sum of a range of cells is $40.00 or higher, then the fee is
> > 15%.
> >
> > I hope that made sense.
> >
> > In other words, the fee is 15% or $40, whichever is higher. 
> 
0
Utf
1/8/2010 9:29:02 PM
Reply:

Similar Artilces:

calculating no of days
How to set the conditional formating such that if current cell which has a date format is at least 14 days from system date, then the cell become black in foreground in Visual basic? Hi use the formula =A1>=TODAY()+14 -- Regards Frank Kabel Frankfurt, Germany crapit wrote: > How to set the conditional formating such that if current cell which > has a date format is at least 14 days from system date, then the cell > become black in foreground in Visual basic? One way, if I understand you correctly: Private Sub Worksheet_Calculate() Dim rCell As Range ...

Inserting date calculations
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm trying to insert something like the following in a document: <br><br>&quot;It has been X days since I last heard from you.&quot; <br><br>Where X, in Excel-speak, would be expressed as ... <br><br>TODAY()-DATE(year/month/day in question) <br><br>The only way I seem to be able to accomplish this directly is to insert a formula field; however, I don't seem to be able to get at the kinds of date functions or information Excel provides. <br><br&g...

calculate null text field
Dear Helpers, I would like to creat a calculated field in a query that merch 3 text fields together, like, Index: [field A]&[field B]&[field C]. I don't want to replace the null field with anything so that if [field A] and [field B] are null and only [field C] contains data, the calculated [Index] field will look exactly like [field C]. Is it possible? I hope it is possible coz the Index field is a major field in my database. Thanks a lot to my helpers!! What happens if you type: [field A] & [field B] & [field C] into the Field row in query design, and choose A...

Calculate the number of seconds between two times
I need to calculate how many seconds are between two times, e.g. 8:08 AM start time and 8:44 AM stop time so I can use the result in a formula. The start and stop times are in neighboring columns. The formula would be result= number of counts / (Stop time-Start time) in seconds to give me a result in counts/second. On Tue, 1 Jun 2010 13:24:01 -0700, Budda Belly <Budda Belly@discussions.microsoft.com> wrote: >I need to calculate how many seconds are between two times, e.g. 8:08 AM >start time and 8:44 AM stop time so I can use the result in a formula. > >Th...

Calculated field 09-04-06
Hi, I have two new fields on a form 1 called deadline and 1 called days to go. deadline contains a date that can be selected from a calendar and I want days to go to contain the calculation of todays date minus deadline. Am very new to this never written a script in my life, can anybody please help Sure. The following is a small script that takes a datetime field as an input and calculates the difference in day, hours or minutes, whatever makes sense. var displayField = crmForm.all.<name of a string field>; var formDate = crmForm.all..<name of a datetime field>.DataValue; ...

Calculating age #2
How can I calculate a persons age from a birthdate in excel? This is a quick way but not the best: If you put the birthdate in A1: =(TODAY()-A1)/365 You will need to format the cell with General formatting. Judith >-----Original Message----- >How can I calculate a persons age from a birthdate in excel? >. > HI, =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW (),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" From Chip Pearson's site: http://www.cpearson.com/excel/datedif.ht...

About Unit calculation with a fixed duration task
Hello, Usually Units = Work / Duration, but in some case it seems this formula doesn’t work. I performed some basic tests in project: First test: I created a fixed duration task: 1 resource, Units = 100%, Work =5d, actual Work=0, so Duration = 5d and Remaining work =5d I changed Rem Work from 5d to 6d, the task become Units= 120%, Work =6d, so Duration = 5d and Remaining work =6d which is fine. Second test: I created a fixed duration task: 1 resource, Units = 100%, Work =5d, actual Work=1d, so Duration = 5d and Remaining work=4d I changed Rem Work from 4d to 5d, the task beco...

how do i set up a spreadsheet to calculate fuel milage
i want to make a spreadsheet that take my total km and liters and converts them to gallons and miles then calculates mpg 1 liter = 0.264172051 US gallon 1 miles = 1.609344 kilometers A = Kilometers B = liters =(A1*1.609344*0.264172051)/B1 OK, I've probably overdone it with the significant digits, but there's your equation. "Dmds" <Dmds@discussions.microsoft.com> wrote in message news:670B36AA-3E5F-4597-889E-81BDB4FF8DDB@microsoft.com... >i want to make a spreadsheet that take my total km and liters and converts > them to gallons and miles then calculates ...

Calculating comboboxes
I have a form that has two fields that are not calculating right. [RemoteNumberHours] = 0.25 [NumberPCS] = 1 Code behind: =[RemoteNumberHours]*[RemotePCS] This should work, but it is giving me a total of 1 instead of 0.25. What am I doing wrong? Thanks Linda Looks to me like the code behind ought to be [RemoteNumberHours]*[NumberPCS] "LRay67" wrote: > I have a form that has two fields that are not calculating right. > > [RemoteNumberHours] = 0.25 > [NumberPCS] = 1 > > Code behind: > =[RemoteNumberHours]*[RemotePCS] > > Th...

Sprdsht not calculating
I have a large workbook: many sheets, many calculations, much formatting. (I know, I know, way too much overhead but it's a corporate mandate to use this sprdsht. I'm working on reducing it.) I have manual calculation toggled on. When I press F9 the percentage indicator scrolls up quickly to 100% and the "Calculate" flag in the lower left corner of the screen disappears. But I can see formulas that are not calculating, and the only way to force a calculation is to press F2 to edit the cell and hit enter to derive the true value. This is not a case of the "Calculate...

Calculated Items in Pivot Tables #2
Dear all, I am new to excel pivot table and I would like to ask you all a question regarding the calculated items/fields in pivot table. I am making a pivot table whose data source is shown below: Team Person Amount_Sold Price_each Paper A 50 35 Paper D 60 25 Pen D 500 4 Desk C 20 400 Desk A 10 700 Pen B 700 3.5 Clip B 500 2.5 Clip C 600 2 Desk B 15 600 I am thinking if it is a way to ad...

Cells Not re-calculating despite autocalc on
I am an advanced excel user and this is the first time i have encountered this problem. My xls contains a mixture of arrays, lookups and sumif functions. MY array is pulling through the updated information (eg. a sum of spend on a category), and this in turn feeds to a total by quarter. HOwever the simple formula which adds the three months of the quarter is not updating. However, if i go to the cell with the forumla (which is as simple as a1+a2+a3) press F2 to edit, then press enter, hey presto it calculates? Weird. any ideas? I dont want to go into each cell and do that as there are th...

Is there a way to parse multiple values in a cell and apply calculations on those values ?
Is there a way to parse multiple values in a cell and apply a calculation on those values ? The Inventory column has a formula to determine the total inventory of the store where each unique item has its quantity appended in parentheses to the item=92s name. Each unique item is separated by a comma. Each item=92s price will change over time and VLOOKUP can be used to determine the current price of that item on another worksheet. What I=92m trying to do is: 1) Parse out the multiple values in the Inventory column on the INVENTORY worksheet 2) VLOOKUP each item=92s current price on ...

How do I automatically calculate ages in Xcel?
I have part of the formula figured out (i THINK) but I'm running into a brink wall. I figured this would be a popular formula, but apparently it's not. The YEAR funtion doesn't work because it apparently only bases it's output on the date's YEAR...so it doens't understand if the person's birthday hasn't come around yet. HELP PLEASE! revkermit@aol.com Try http://www.cpearson.com/excel/datedif.htm Regards, Peo Sjoblom "revkermit" wrote: > I have part of the formula figured out (i THINK) but I'm running into a brink > wall. I f...

Calculate age
Hi there, How do I calcuate the age of a person if all I have is their date of birth in a cell? Is this possible in excel? Thanks for your help Suzanne One way: =DATEDIF(A1,TODAY(),"y") See http://cpearson.com/excel/datedif.htm for more options. In article <951FF122-C82D-4A48-88BE-FD29E288C8DD@microsoft.com>, Grd <Grd@discussions.microsoft.com> wrote: > Hi there, > > How do I calcuate the age of a person if all I have is their date of birth > in a cell? > > Is this possible in excel? > > Thanks for your help > > Suz...

calculating percentages #4
I'm trying to calculaate 2 percentages: 1. H2/D2 * 100 2. G2+H2/D2 * 100 Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=1ec12eee-65e9-44e3-bd59-5614166fd0aa&dg=microsoft.public.excel.mis...

Calculating Total
Hi there, I run the following code: Private Sub FShare__Coke_2LtPET_Exit(Cancel As Integer) [FShare: Total].Value = [FShare: Total].OldValue - [FShare: Coke 2LtPET].OldValue [FShare: Total].Value = [FShare: Total].Value + [FShare: Coke 2LtPET].Value Form.Recalc If [FShare: Coke 2LtPET].Value = 0 And [Price: Coke 2LtPET] <> 0 Then result = MsgBox("Price info entered - needs F Share info", vbOKOnly) Cancel = True End If End Sub However, for some reason the result blanks out any information in this field after this fun...

Calculating the average of cells
I am trying to use a formula to calculate the average of random cells currently containing data, however also including cells where data will be added at a later date but remain empty. Is this possible? On Mon, 13 Mar 2006 12:53:27 -0800, pippa <pippa@discussions.microsoft.com> wrote: >I am trying to use a formula to calculate the average of random cells >currently containing data, however also including cells where data will be >added at a later date but remain empty. Is this possible? =average(<range1>,<range2>...) will work quite happily with blanks and no...

Calculating specifc byte positions in cells
Hi, Here is what I am trying to do: I have a spreadsheet that keeps track of employees vacation and sick time. The spreadsheet is formatted to column A is the employee name, columns B - AB are days of the month. I would like to have the manager be able to enter in the time off as either S8 or V8 (8 representing the number of hours off the employee had S = sick V = vacation). I would like Column AC to then calcuate the total sick hours and AD to calcuate the total of vacation hours. I played around with a few formulas but could not figure out a way to do it. Is there a way to have this d...

Pivot calculated field
I want to include the right hand column inside the pivot table. It wa calculated externally for this example. The formula is Plan Pct Actual Total. [image: http://www.pbase.com/image/25606965/original.jpg] The formula for the green bordered cell is 49.64% * 3,132,547. I cannot get percent of total in a calculated field, nor can I use th column total in a calculated field. Is this possible? BTW, I cannot get the image to appear inside this note - please clic to see - thanks. TI -- Message posted from http://www.ExcelForum.com I wasn�t able to find away either. What I did was to ...

Excel
How to setup "Automatic Calculation" as the default setting? Tools/Options/Calculation/Automatic -- Ken Russell kenrussellyourhat@optushome.com.au Remove yourhat to reply by e-mail .. "NewUser" <NewUser@discussions.microsoft.com> wrote in message news:8553FFB2-487F-4265-BCB1-E72D8F372E8E@microsoft.com... > How to setup "Automatic Calculation" as the default setting? Excel always opens in auto calc mode, but the calc mode setting in the first workbook opened will change it to manual if that's how it is set. The first workbook opened includ...

I want to calculate the grade I need to get an A in my class
-- Neal's Quest A 95% average should get you close. But I'd talk to the instruction to see what the grading scale is. Neal wrote: > > -- > Neal's Quest -- Dave Peterson ...

Re: How to calculate frequency of lottery numbers?
http://www.txlottery.org/export/sites/default/Games/Lotto_Texas/Number_Frequency.html 19 49 17 48 7 47 31 45 26 43 8 42 10 42 12 42 23 42 35 42 36 42 43 42 16 41 28 41 42 40 47 40 -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Don Guillett" <dguillett1@austin.rr.com> wrote in message news:... >I once played the Texas lotto and kept a log of winning numbers. Probably >best to use a macro to record as each game is played. However, my file >...

Display currency as calculated
Gyus I am just about finished but I can't get Excel to display my currency as the calculated result. I need to display this accurately so I can take the figures to my accountant. My issue is that when I calculate the GST on an amount, the calc is fine but is displayed in correctly eg $22.63 / 11 = 2.05727272727273, so I need it to display it as $2.05 not as it does - $2.06 (I am sure the taxman would like this printout). I looked at the TRUNC command but can't get it to work. Is there a way to do this in code to a column of numbers where every row has been summed(horiz...

Calculating Hours
I am looking to calculate hours and minutes. I want to calculate how long something has been running for by adding hours and minutes every week to the total. The problem is it stops counting after about 1000hours. is there a formula to count hours and minutes up to say 10000hours. It I put 500:45:00 in a1 and &00:45:25 in A1 The in A3 =A1+A2, I get 1201:30:25 I do not understand "it stops counting after 1000 hours" please give more details of your worksheet's layout best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from ema...