In a formula, how do I set a maximum acceptable value?

```I need to calculate my loss on a rental property but the loss cannot be
greater than \$25,000 no matter how large it actually is. How do I write an
Excel formula that will include this maximum value limit?
```
 0
Utf
3/6/2010 1:39:47 AM
excel.worksheet.functions 4936 articles. 2 followers.

1 Replies
904 Views

Similar Articles

[PageSpeed] 27

```Use the Min function, as in:
=min(25000,yourformula)

Regards,
Fred

"hollisterbulldawg" <hollisterbulldawg@discussions.microsoft.com> wrote in
message news:E8CCDA8F-F60E-4D48-BDE8-8B35760E2BD3@microsoft.com...
>I need to calculate my loss on a rental property but the loss cannot be
> greater than \$25,000 no matter how large it actually is. How do I write an
> Excel formula that will include this maximum value limit?

```
 0
Fred
3/6/2010 1:44:23 AM

Similar Artilces:

Formulas to (a) pull month, year and (b) sum within date range?
I have a list of dates in column A, and numbers down cols B-E. Off to the right, I need a table that lists the month/year and the totals for each of the columns. It needs to be a separate table, rather than a pivot table, so it can be copied-and-pasted by others. What I _think_ I need is: -- a formula in H2:H10 (maybe more) that will look down the full range of dates (call it A2:A100) and put one month/year per row >> H2 = 02/09 H3 = 03/09 H4 = 04/09 etc. -- a formula in I2 that will sum all entries in B2:B100 with the month and year of H2 I think I could get the secon...

setting form record source when form is opened
I have a question about setting the record source of a form at the time the form is opened. Background: I need to clean up and modify an application. The application currently only has one table and one template form for data entry. However a separate copy of a selection query and a separate copy of the data entry form have been created for each analyst in the office. The only difference between the queries is the CurrentAnalyst criteria is set to one personâ€™s name. The only difference between the forms is the record source is set to a different selection query. Currently the analysts o...

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

How do you ensure that null-valued elements are not serialized?
I am using complex types in order to support serialization/deserialization of floating point numbers, since floating points can't be null. I've seen how to suppress attributes that are "not specified", such as having a float member called Value, and a bool member called ValueSpecified. This instructs the XML Serializer to omit that attribute altogether if it wasn't "Specified". But how can I tell it to omit the XML element altogether? Here's the problem: I deserialize an object that looks like this: <person> <name>Joe<...

Combo Box default value
I am trying to set a default value for a combo box that is a static list with three items in it. Everything I have found googling has related to setting the default value in the controls properties using this format: =[controlname].ItemData(indexnumber) Using this suggestion hasn't worked so far and I am sure it is a simple solution. Thanks. Sorry, it is on a form. I want the default value of the combo box on the form to be the first item in the list. Thanks "Ofer Cohen" <OferCohen@discussions.microsoft.com> wrote in message news:EEEFDE47-409A-4D08-B426-0374F...

Formula help *
Office 2007 I am trying to display in a cell the last value in a selected column. Numbers are added daily to this column but I need this specific cell to show the last entry at all times - any ideas or help would be appreciated Thanks in advance Bryan Sun, 10 Feb 2008 20:53:28 +0900 from JohnWA <j_ustforfun@hotmail>: > Office 2007 I am trying to display in a cell the last value in a selected > column. Numbers are added daily to this column but I need this specific cell > to show the last entry at all times - any ideas or help would be appreciated =OFFSET(\$A\$1,COUNTA(\$...

Best Practice for Set up your internet address and 2 different domains
I want to make sure i get this right the first time. I am setting up an SBS 2008 server and am to the point of setting up the domain. I have 2 - but, both are for different things. name1.com = remote access (don't need for e-mail, just RWW) name2.com = e-mail for company (don't need for RWW, just e-mail) It is my understanding that the "Set up your internet address wizard" will setup exchange (which i will need for name2.com) as well as remote access (which is name1.com)... Another facet is this: i'm not using Exchange completely for e-mail, I am usign th...

Insert spaces between pasted values #2
Hi! Is there a way to insert spaces between values that are pasted? For example, i have in rows 1,2,3 values a,b,c and i want to paste them into another list in rows 1,3,5 respectively (with a single-row space between pasted values). Thanx! M I presume you have more than just 3 rows. A general approach would be to use a helper column next to your data and to fill a simple sequence down that column for as much data as you have. Then copy all the numbers that make up that sequence and paste them immediately below the sequence in the same column, so that if you have 200 numbers for example the...

How to set default zoom scale for IE8?
Does anyone have any suggestions on how to set default zoom scale for IE8? I am using XP with SP3, the default zoom scale is 100%, and I would like to change it into 135% as default. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric Click the "Change zoom level" arrow at the bottom right corner in IE8, click "Custom" and set it to 135%. Click Tools, Internet Options, Advanced. Uncheck "Reset zoom level for new windows and tabs". Click OK. -- Regards, Ramesh Srinivasan, Microsoft MVP [Windows Desktop ...

Excel formulas #9

= if formula needed
good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i cant get it right. i have tried various variations of the formula.any help is appreciated. thx wyn -- wynb in you example is the cell you want updated "Q1"? If so than it should read =IF(Q80="closed",NOW()) -- Kevin "WYN" wrote: > good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i > cant get it right. i have tried various variations of the formula.any help is > appreciated. thx wyn > -- > wynb In that formula yo...

User Defined Function vs. Named formula
I have used Named Formulas and am now learning about User Defined Functions. One thing I found with named formulas is that the naming conventions around global/local selections. Reading about UDFs makes me think they might be a better option for a lot of my work. Any insight as to When to use one over the other will be appreciated. Thanks Robert IMO they are different beasts. A named formula would be used to shorten formulae by including a name rather than the sub-formula (especially where it is repeated), and to improve readability/maintainability, as the name would/should be meanin...

Simple If, Then formula for excel
Hi all, So I am fairly weak when it comes to Excel formulas, so I'm sure what I require is really simple, I just have no idea how to formulate it. Basically, I have a column of values, some of which are negative values and some are positive, but they are changing from month to month (i.e. accruals and prepayments). I need to add up all the negative values from the column in a new cell, and all the positive values in another. So it will be something like If amount>0 then add to cell, else add to other cell. So there you have it, really simple I just have no idea what to do. So i...

Excel Formula #8
I am trying to format spreadsheet. Need 3 counts. 1st count is for 90% and above =COUNTIF(H5:M16,">89.99"). Second count is for Under 80% =COUNTIF (H5:M16,"<79.99") . Can anyone help me with a formula for 3rd count, between 80% and 89.98? I can't seem to formulate it. Thanks in advance Hi Barb, Try =COUNT(H5:M16) - COUNTIF(H5:M16,">89.99") - COUNTIF(H5:M16,"<79.99") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Barb" <...

converting text values to number & decimal values..
I have any array which read a csv file and I am using split and populate these fields into my table.. temp= 5245,test1,23.45 temp1=3456,test1,23.45 Now I want to convert my text fields to number and decimels respectively. I tried the following functions but this works fine if they are number and fails it was text value. TESTID = CInt(sampleid) 'CInt(myarray(1))Convert to number CT_num = CDec(ct_val) -- Message posted via http://www.accessmonster.com Hi, Test to see if they are numeric first. If IsNumeric(sampleid) Then TESTID = CInt(sampleid) Else ...

How do I change the values in loan amortization
I am wanting to use the loan amortization spread sheet, and was wondering how you change the values as I want to change Loan period in Years to calculate Loan period in Months?? I have tried several things but not having a great knowledge on using Excel im failing miserably! PLEASE HELP!!! :O) Thank you! a combination of dividing by 12 and multiplying by 12. play with it -- Don Guillett SalesAid Software dguillett1@austin.rr.com "ROXYAWORLD" <ROXYAWORLD@discussions.microsoft.com> wrote in message news:46CED19F-2119-43B7-8BC1-57710F6E008D@microsoft.com... >I am wan...

Is there a maximum number of values entered into a pick list?
For our project we need to add a very large amount of values into a pick list. 1839 values to be precise. When and existing entity that contains the pick list is opened we get an error. The event view contains the following error: MSSQLSERVER Error: 17805, Severity: 20, State: 3 Invalid buffer received from client. The strange thing is that when we create a new record we do not receive and error. We receive the error when attempting to open up a saved record. Has anyone seen this issue? ...

Perspective setting won't save in a 3-D column chart
I have a 3-D column chart that has the perspective setting as 20, the elevation as 24 degrees and the rotation as 198 degrees. I change the settings, close the dialog box and click save. Yet the perspective setting resets itself to 15 every time I open the file. I am using Excel 2007. Any suggestions? Thanks. Hi Kathi, I have been able to duplicate your problem, I suggest this is a bug, although there might be some reason for this behavior, I can't think of any. It's probably not worth it but I suppose you could attach a macro to the Open_Workbook event and reset it to...

SQL OPtions when setting up new user
In version 7.5, there was options for updating the SQL server with user information, (checkboxes) I cant find that in version 9. What is happening is we add users to PDK, and have that program update the SQL database with the user information. Now when I try and add a user to Great Plains, I get the message that the user is already a user in the database. Where can you turn the option off to update the SQL database and just add the user to Dynamics? thanks! -- Doug Same question. Please advise! "Doug" wrote: > In version 7.5, there was options for updating the SQL...

changing categories settings
Why is that when I go to set up a category on the category list, I can't find the option of "special - category (transfer to or credit card payment)" in the list? Why can't I have the credit payment category on my expenses report? -- sshilder In microsoft.public.money, suzana wrote: >Why is that when I go to set up a category on the category list, I can't find >the option of "special - category (transfer to or credit card payment)" in >the list? I am guessing you would want to set Tools->Settings->AccountSettings->DisplayAllAccount...

How to set Outlook files for backup using the Backup Tool?
Using the Personal Folders Backup Tool in Outlook 2007 using Vista, I highlight the top level folder in my "All Mail Items" list (labelled as Personal Folders) and click File/Backup but under the direction "Save backup copies of these personal folders files" the dialog box says "(No files set for backup)." When I click on "Options," the next dialog box shows one subfolder name with a checkbox to mark if you wanted to back up its files, but not any others. There are a lot of other subfolders whose files I want to back up, but they are not ...

In Excel how can I assign values to a Yes or No question with IF
I am building a spreadsheeet, where If a cell has a "Yes" in it is is work ..75 and a "no" equals 0. I know a IF statement will help, but I don't know how. I also want Excel to caculate from an inputted date how many moths ago it was. Can anyone help? Craig =IF(A2="Yes",.75,0) is all that you need -- HTH Bob Phillips "craigscoop" <craigscoop@discussions.microsoft.com> wrote in message news:72842B75-8485-497C-8AC6-01348498D73B@microsoft.com... > I am building a spreadsheeet, where If a cell has a "Yes" in it is is w...

Setting length of all cells in a column
I have a column in a spreadsheet that holds bank account numbers. Unfortunately, although a bank account number has to be 8 characters long (e.g. 00012345), some of the leading zeros have been dropped, meaning that the cell > only contains, for example, 12345. Some fields only have one leading zero, so the field has been dropped to 7 characters long, whilst some have no leading zero so are already the required 8 characters long. There are 8000 lines on this spreadsheet so changing the lines manually is not feasible. Any idea how I could quickly change those fields that are not alr...

checking duplicate values on data entry
Dear all, In my table design,Because I need to accept No value , I can not use NO duplicate index for my field , but I shouldn't let duplicate values in it. to develop the need I check the value in the data entry form using "Find first" of the record set. On my own computer it is OK and no duplicate value can be entered. but users CAN add duplcate value.(mine is office xp but some use office2003). what should I check? and if there are better solutions please let me know. tnx in advance. The version of Access should make no difference. How are you using FindFirst a...

Set reminder when importing into Outlook
Does anybody know how to set the reminder on when importing meetings from Excel to Outlook? What do I need to put in the Excel column to make Outlook read that I want it to turn on? ...