#### IF statement that yields a value based on a formula

```Help!  If cell B12 >= 0, then I need the answer to be calculated based on the
formual "B12 * .40".  If cell B12 < 0, then the answer is zero.

How do I do this?
--
barbara h
```
 0
Utf
2/11/2010 4:59:01 PM
excel.worksheet.functions 4936 articles. 2 followers.

6 Replies
544 Views

Similar Articles

[PageSpeed] 32

```IF(B12>=0,B12*0.4,IF(B12,0,0))
Click yes if helped
--
Greatly appreciated
Eva

"barbara h" wrote:

> Help!  If cell B12 >= 0, then I need the answer to be calculated based on the
> formual "B12 * .40".  If cell B12 < 0, then the answer is zero.
>
> How do I do this?
> --
> barbara h
```
 0
Utf
2/11/2010 5:14:01 PM
```Hi,

Try this

=MAX(0,B12*0.4)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"barbara h" wrote:

> Help!  If cell B12 >= 0, then I need the answer to be calculated based on the
> formual "B12 * .40".  If cell B12 < 0, then the answer is zero.
>
> How do I do this?
> --
> barbara h
```
 0
Utf
2/11/2010 5:15:01 PM
```One way...

=(B12>=0)*0.4

--
Biff
Microsoft Excel MVP

"barbara h" <barbarah@discussions.microsoft.com> wrote in message
news:CEA214BD-9E90-4D3A-A92B-FDB2C3FEC8C7@microsoft.com...
> Help!  If cell B12 >= 0, then I need the answer to be calculated based on
> the
> formual "B12 * .40".  If cell B12 < 0, then the answer is zero.
>
> How do I do this?
> --
> barbara h

```
 0
T
2/11/2010 5:19:50 PM
```>>based on the formual "B12 * .40".
>=(B12>=0)*0.4

Ooops!

Disregard that formula.

Here's a corrected version:

=(B12>0)*(B12*0.4)

--
Biff
Microsoft Excel MVP

"T. Valko" <biffinpitt@comcast.net> wrote in message
news:ePHMs5zqKHA.3464@TK2MSFTNGP06.phx.gbl...
> One way...
>
> =(B12>=0)*0.4
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "barbara h" <barbarah@discussions.microsoft.com> wrote in message
> news:CEA214BD-9E90-4D3A-A92B-FDB2C3FEC8C7@microsoft.com...
>> Help!  If cell B12 >= 0, then I need the answer to be calculated based on
>> the
>> formual "B12 * .40".  If cell B12 < 0, then the answer is zero.
>>
>> How do I do this?
>> --
>> barbara h
>
>

```
 0
T
2/11/2010 5:42:58 PM
```Is the second IF really needed? Would not IF(B12>=0,B12*0.4,0) solve the
problem more simply?

Also, since 0*0.4 = 0 it could be simplified to IF(B12>0,B12*0.4,0).

If the test only occurs a few times there won't be a noticable difference,
but if the test happens hundreds of times then the last formula will be
faster.

"Eva" wrote:

> IF(B12>=0,B12*0.4,IF(B12,0,0))
> Click yes if helped
> --
> Greatly appreciated
> Eva
>
>
> "barbara h" wrote:
>
> > Help!  If cell B12 >= 0, then I need the answer to be calculated based on the
> > formual "B12 * .40".  If cell B12 < 0, then the answer is zero.
> >
> > How do I do this?
> > --
> > barbara h
```
 0
Utf
2/11/2010 6:16:01 PM
```Neil,

You don't need an IF statement at all, have a look at other responses
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"Neil Humphries" wrote:

> Is the second IF really needed? Would not IF(B12>=0,B12*0.4,0) solve the
> problem more simply?
>
> Also, since 0*0.4 = 0 it could be simplified to IF(B12>0,B12*0.4,0).
>
> If the test only occurs a few times there won't be a noticable difference,
> but if the test happens hundreds of times then the last formula will be
> faster.
>
>
>
> "Eva" wrote:
>
> > IF(B12>=0,B12*0.4,IF(B12,0,0))
> > Click yes if helped
> > --
> > Greatly appreciated
> > Eva
> >
> >
> > "barbara h" wrote:
> >
> > > Help!  If cell B12 >= 0, then I need the answer to be calculated based on the
> > > formual "B12 * .40".  If cell B12 < 0, then the answer is zero.
> > >
> > > How do I do this?
> > > --
> > > barbara h
```
 0
Utf
2/11/2010 7:01:01 PM

Similar Artilces:

Using Office2000 Sp3 I use Excel a lot for historical things including eg; areas of land expressed in ACRES ROODS PERCHES which is not added in tens (similar to �/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a formula which when applied to entries across 3 columns gives a decimal answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in D1, Roods E1, perches F1. Once this is decimalised I can work out ratios etc. However, I occasionally get tabbed text files or excel files from people who have entered lots of raw data as eg ARP where it would be ...

How to: text color depend on value
Anyone know how to make the text change colors, depending on what valu certain data is? I'm sure it's easy... -- bobsmith58 ----------------------------------------------------------------------- bobsmith58d's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1452 View this thread: http://www.excelforum.com/showthread.php?threadid=26148 bobsmith58d Wrote: > Anyone know how to make the text change colors, depending on what valu > certain data is? I'm sure it's easy.... Go to Format/Cells/Custom and inside the "Type" box, choose th...

How to determine the values? 06-05-10
Does anyone have any suggestions on how to determine the value? For example, Under following columns: [A] [B] [C] [D] 1 174 198 222 7 180 204 228 9 182 206 230 A given number is 204 in cell E1, I would like to determine the value under column A, which should return 7, because 204 is under the same row. A given number is 174 in cell E1, I would like to determine the value under column A, which should return 1, because 174 is under the same row. Does anyone have any suggestions on how to do it in excel? Thanks in advance for any suggestions Eric Try this array formula** : ...

How do I sum values that may contain "#N/A" in Excel?
=SUMIF(Range,"<>#N/A") or remove the cause that give the #N/A result =IF(ISNA(A2),"",A2) -- Regards, Peo Sjoblom "bakerbabe" <bakerbabe@discussions.microsoft.com> wrote in message news:6234E075-3C49-4182-83CB-23CCB10186C3@microsoft.com... > =SUM(IF(ISNA(D1:D5),0,D1:D5)) enter as an array formulas - use SHIFT+CTRL+ENTER -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "bakerbabe" <bakerbabe@discussions.microsoft.com> wrote in message news:6234E075-3C49-4182-83CB-23CCB10186C3@microsoft.com... >...

Rounding Value Up to Whole Number
I have a report footer that I'm adding values from the detail section. Many of my values that I'm adding are .333. Once I sum the value in the footer, I end up with not a whole number, for example 9.999. Is there a property I can set to bring the value to the next whole number. Thanks for your help Try =- Int( - [MyField]) (note there is a - before the Int and before the my field) Substitute the name of your own text box) Evi int(number*100 + 0.5) / 100 "PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message news:CBDE76F7-8837-4223-BF5C-E...

How I can pick a specific value on an array in excel
Given a table with A to H columns by 1 to 20 rows return an specific value. I would like to enter column ID and row ID to get back the value where both intersect. One way http://www.contextures.com/xlFunctions03.html#IndexArg Regards, Peo Sjoblom "Paul" wrote: > Given a table with A to H columns by 1 to 20 rows return an specific value. I > would like to enter column ID and row ID to get back the value where both > intersect. ...

Purchase Order Formula
After recently finding out that the Shipping Field does absolutely nothing in a purchase order reguarding Costs of Goods, or anything for that matter, I decided that "oh, I can just use a Formula to change the costs in my purchase order to reflect my shipping costs!" So, after going through and adding up all the quantities of items recieved, and dividing it by my shipping cost, I got a cost of \$1.40/item. No problem right? Wrong. The Formula will in no way reflect a NEWLY updated cost. And I cannot use Formulas after the purchase order is committed!!!!! >:O FOR EXAMP...

Receivables Statement Ranges
How can I get the User Defined Field 2 into the drop down box for Ranges on the Print Receivables Statements page? I see the customize User Defined Field 1 in there but not the second after I have customized it and added data into the field. Is there something I am missing? Thanks! No, the first field is automatically provided in the search but not the second. This would require a modification by a programmer -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublicat...

Outllok Rule Creation based on Greater Than Numbers...
Program: Outlook 2003 He Everyone, My boss has requested the creation of a particular type of rule that has me stumped. He receives many quote requests per day but we are only interested in them if they are BELOW a certain quantity, i.e. we would be interested in fulfilling an order for less than 1000 but not more than that. I have tried filtering based on "Specific words in the body" with entries such as ">1000," (hoping Outlook would know to treat the entry as a number above 1000, also tried "1,***," (hoping it would recognize as a number w/wildcards)...

Copying formulas #12
Hi all, I have a row of forumulas, which relate to columns of data. Each formula needs to be copied down the sheet, but I need the column value to change, not the row value. eg C3 : =sum(c2:c31) C4 : =sum(d2:d31) C5: =sum(e2:e31) etc. How can I replicate the formulas down, but get the Column values to change ? TIA Stu Ignoring the issue of Circular references (the formula in C3 references a range that includes C3)... I *think* this will work for you Put the formula =SUM(C\$2:C\$31) into an empty row in your spreadsheet and copy it across to all the columns you want to su...

help with if formulas
I think that this is fairly simple but I still need help. Could anyon tell me what formula to use if I want to have column d show me a valu of 8 if the value of column c is greater then 5 hrs but show me a valu of 0 if the column c is less then 5 or blank? Thank -- rvnwd ----------------------------------------------------------------------- rvnwdr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2390 View this thread: http://www.excelforum.com/showthread.php?threadid=38119 =IF(C1>5/24,8,0) format the result as time. If C1 has 5 hrs as integer 5, do not ...

field returns a value if at least one matching record is found
I had posted this question in the Forms thread, but that was probably the wrong place. TblPartNumInfo TblDefectRpt My query for PartNumInfo brings together lots of different tables including customers, contact info, status of jobs. A feature I need to add is a warning if at least one defect report exists for a given part number. It would be shown on the form as a conditional format of the text box where the part number is shown. So I would like to add a field to my query that would return some value (like the part#ID) if at least one Defect Report exists for a part#. I created a sepa...

formula Help needed
Hi I have two worksheets one is purchases and another is named Bank on purchases I have 4 columns :::::A::::::::::::::::::::::::::B:::::::::::::::::::::::::::C: 1::::: WHSmith 2::::DATE----------Dr------------Cr 3::::2-10----------�480.18------(Formula needed) Then I have Bank DATE----------Details---------Dr------------ Cr 9-10-----------WHSmith------ 0-----------�480.18 What can I put in C3 to lookup for �480.18 in Bank worksheet and return the value in C3????? TIA hope I explained it clearly.. Do you just want whatever is in the one cell to appear in anothe...

Suppress Main Section Based on Subreport
Hello: In Crystal XI, I want to suppress sections of the main report when either of the two subreports that I have on my main report are blank. Specifically, each of these two subreports does not always show data. That's because I have configured the one and only field in each subreport to suppress itself when that field is greater than or equal to 0. In any case, if either of these two subreports are not displaying data, I do not want sections of the main report to display data. And, that's true if those sections contain either of these two subreports, any oth...

formula doesn't work
Hi, I am using window XP, have column B, C and D. Cell C1 and B1 are the numbers. I try to enter a formula into column D, it keeps give me C1/B1*100 in cell D1. Why it won't give me the numbers in D1? Is this my Exel's setting problem? -- JJ Hi JJ Instead of C1/B1*100 try =C1/B1*100 HTH. Best wishes Harald "JJ" <JJ@discussions.microsoft.com> skrev i melding news:50C9EF25-AFA5-4397-B01C-83E2B49DC6B1@microsoft.com... > Hi, > > I am using window XP, have column B, C and D. Cell C1 and B1 are the numbers. > I try to enter a formula into column D, it kee...

Avoiding Nested 'IF' Statements
Hi, I have seven cells (A1 - A7) with drop-down lists that allow the user to select one of a number of text options. The default text option is the word "None". I have an eighth cell that needs to display a text message whenever any one of the seven cells is displaying any text other than "None". That is, if one of the seven cells is NOT "None", then the message should display. Is there a more elegant formula for achieving this, other than nesting lots of 'IF' statements? Thanks, -- Mike -Please remove 'safetycatch' from email address b...

Increment a number based on four adjoing cells
I am using an xlsx spreadsheet for indexing metadata for file folders. Based on the metadata selected from dropdowns in columns H, I & J, the spreadsheet builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined number; nnnnn= sequential number). I'm using the following formulas: Col B =VLOOKUP(H577,FUNCTIONLU,2) Col C =VLOOKUP(I577,CATEGORYLU,2) Col D =VLOOKUP(J577,TYPELU,2) Col E =IF(D576="","",IF(COUNTIF(\$D\$2:\$D576,\$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF(\$D\$2:\$D576,D576)>1,INDEX(\$E\$2:\$E576,MATCH(\$D576,\$D\$2:\$...

Default site on SOP transaction based on order type?
We have an inspection site set up that we use for all material that is returned to us. I'd like to customize SOP entry so that the site id defaults to this inspection site for returns. Otherwise the standard defaults for the site id would apply. Has anyone done this type of customization? Any suggestions / pointers would be appreciated. -- Jim@TurboChef VBA would enable you to add this functionality. The code is fairly simple. -- Charles Allen, MVP "Jim@TurboChef" wrote: > We have an inspection site set up that we use for all material that is > returned to ...

Excel formulae start doing strange things
Has anyone had the problem where the result of a formula is incorrect. I've had on occasion entered a simple formula, such as adding a group of numbers within an "IF" statement and the result is wrong. If I start a new workbook, and enter the exact same thing, it works out correctly. Yesterday one of my co-workers had a problem with a spreadsheed she's been using for years. All of a sudden, she started getting "VALUE" errors in the lower half of the spreadsheet. All formulae were the same in each row. I ended up fixing the problem by deleting 5 rows, then entering ...

VLOOKUP formula displays in cell, will not return data
I had an older version of Excel (97 maybe?) and our office updated to 2007. Now my vlookup will not return data. The vlookup formula appears in the cell or I get the NA#. I've formatted my lookup cells and table to General but still get the same result. After formatting the cell as General, re-enter it (F2/Enter) - if you're still seeing the formula, press Ctrl/~ -- you're looking at the formula layer instead of the value layer. "Chaps" wrote: > I had an older version of Excel (97 maybe?) and our office updated to 2007. > > Now my vloo...

Count certain values
I have a spreadsheet that has column A with names, column B with their choice of meal. Example: A B John Steak Bill Chicken Joe Salmon May Steak Jean Chicken What I want to do is count how many of each dish are required (how many steaks, chickens to order) Can someone please show me the formula? Thanks Probably the easiest way would be to create a "master, unique" list of all available meals, say in Column C. Then, with names and choices filling A1 to B100, try this in D1: =Countif(B1:B100,C1) And copy down as needed. -- HTH, RD --------------...

Importing text to chart based on link to cell
I am attempting to link a cell from my spread sheet to a text box in my chart, but have been unable to do so. Does anyone know how or if this is possible. On Fri, 7 Nov 2003 12:59:29 -0800, Stephen <stephen.szyndrowski@telus.co= m> = wrote: > I am attempting to link a cell from my spread sheet to a > text box in my chart, but have been unable to do so. Does > anyone know how or if this is possible. Select the chart, then click in the formula bar (above the chart, next t= o = the little "fx"), type the equal sign, then click on the cell that you = wish to refe...

increase the timeout value of DSAccess
Experts, How can I increase the DSAccess timeout setting? I know that DSAccess in Exchange 2003 keys on one specific GC, and if that GC is unavailable to it an event will be recorded in the application event log. But how often does Exchange query DSAccess and can I increase the timeout value, if any? -- Spin Hi Spin Actually, DSAccess will use as many GCs are available within a site (up to 10) and will load balance between them. It will use only one configuration DC for write activities. This makes sense given that there is potential for replication conflict if writes are made...

sum value then insert row
How can I make for loop? the conditions are: if total value column height = 5 and column Article = "bike" then copy second row to row 6 (therefore 1 row added between file 5 and 6) thanks for your response ...

Expression for Percentage Calculations Based Upon Subsets of Field
How do you create an expression that will calculate a percentage based upon subsets of two fields? For example, one field is "Status" (there are three options), the other is "Resolved" (populated with a "Yes" in the appropriate cells that correspond to those "Status" entries that are resolved. I must calculate the percentage of "Resolved" entries for each status classification based upon the total number of each status classification. I have created parameter queries that appropriately identify the number of matching records for each S...