formula within IF function

How do I use a formula within the IF function?  What I want to do is check if
a number is less than or equal to an amount, calculate one thing, if it is
not, calculate another.  I tried this:  IF(B21<=150,"=B21*E32","150*E32)
But I only get text returned.

Thanks
--
Shirley 0 7/6/2005 1:16:13 PM excel.misc  78881 articles. 5 followers. 4 Replies 455 Views Similar Articles

[PageSpeed] 0

Try...

=IF(B21<=150,B21*E32,150*E32)

Hope this helps!

"Shirley" <Shirley@discussions.microsoft.com> wrote:

> How do I use a formula within the IF function?  What I want to do is check if
> a number is less than or equal to an amount, calculate one thing, if it is
> not, calculate another.  I tried this:  IF(B21<=150,"=B21*E32","150*E32)
> But I only get text returned.
>
> Thanks 0 7/6/2005 1:19:41 PM
Try

=IF(B21<=150,B21*E32,150*E32)

I assume that B21, and E22 are numeric.

"Shirley" wrote:

> How do I use a formula within the IF function?  What I want to do is check if
> a number is less than or equal to an amount, calculate one thing, if it is
> not, calculate another.  I tried this:  IF(B21<=150,"=B21*E32","150*E32)
> But I only get text returned.
>
> Thanks
> --
> Shirley 0 7/6/2005 1:21:04 PM
Remove the " marks from your formula.

--
Message posted via http://www.officekb.com 0 7/6/2005 1:21:17 PM
Maybe a slight variation:

=E32*MIN(B21,150)

HTH.
--
Dana DeLouis
Win XP & Office 2003

"Shirley" <Shirley@discussions.microsoft.com> wrote in message
> How do I use a formula within the IF function?  What I want to do is check
> if
> a number is less than or equal to an amount, calculate one thing, if it is
> not, calculate another.  I tried this:  IF(B21<=150,"=B21*E32","150*E32)
> But I only get text returned.
>
> Thanks
> --
> Shirley 0 7/6/2005 1:46:20 PM Similar Artilces:

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!\$B\$1:\$BA\$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

Formula In Crystal Reports
Hi All, I'm just starting to learn Crystal right now but it's all very new to me so I was just curious if anyone knows if you can do formulas in it. I'd like to take a custom CRM field called setup cost on a product and add all the values of this field up in a quote. Ie if there are 3 products all with seperate setup costs I want to add those together. Or will this have to be done a different way? Thanks all Tom Hi, Crystal supports both VB and Crystal Reports formulas. the help files are quite good regarding formulas, and the 9.2.2 CR with enhanced edition has an excel...

Excel Formula #6
Is there a way to set a formula to calculate how many rows above (COUNT) with no text or numbers in the column? try =COUNTBLANK(F3:F9) -- Don Guillett SalesAid Software donaldb@281.com "deniseS" <dstafiej@dykema.com> wrote in message news:150b101c3c3fa\$68119950\$a601280a@phx.gbl... > Is there a way to set a formula to calculate how many rows > above (COUNT) with no text or numbers in the column? > =COUNTIF(A1:A100,"") -- HTH. Best wishes Harald Followup to newsgroup only please. "deniseS" <dstafiej@dykema.com> wrote in message news:15...

Changing my functions to use subtotals?
I have 2 functions that are calculating my data correctly, except now I need to consider the autofilter. I figure using subtotal is the way to go if I want to disregard filtered out rows. Also, I plan on moving my data to another sheet, so it will need to reference the current sheet, which is AAT_Raw_Data. Finally, the # of rows of data will vary, but I don't think it will ever be over 10,000. I'm using Excel 2003, so I think there's an issue with using something like "A:A" in my formulas. Can someone help me change these to add in a subtotal and take into...

Formula too long error...
Hello all. I have the following formula in a cell that works just fine. However, I need to add one more SUMIF statement but I get an error stating that the formula is too long. Is there an easy way around this - thank you. NWO =SUM(IF(GoodBoth!\$F\$2:\$F10000="ME",IF(GoodBoth!\$R\$2:\$R10000="E11",IF(GoodBoth!\$Q\$2:\$Q\$10000<=20011231,IF(GoodBoth!\$S\$2:\$S\$10000<>"110",IF(GoodBoth!\$S\$2:\$S\$10000<>"115",1,0))))))+SUM(IF(GoodBoth!\$F\$2:\$F10000="ME",IF(GoodBoth!\$R\$2:\$R10000="E12",IF(GoodBoth!\$Q\$2:\$Q\$10000<=20011231,IF(GoodBo...

Refreshing a User Defined Function
To all: On Sheet 1 of my workbook, I have a date in cell A1. In the next sheet, I want the same date as the previous sheet plus seven days. When I copy the latest sheet, I want it to reference the sheet located prior to it rather than referencing the original sheet (Sheet 1). After copying 52 worksheets, I want each of them to reference the sheet located just prior to each of the sheets. I found this User Defined Function posted here on this discussion site and it works great except for one thing. If I change the original date on Sheet 1, the UDF does not automatically update. Is...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

Create Assembly Call
hi All, can the create assembly call under update entity be used to add a certain number of hours/days/months/years to a date. i am trying to do this workflow if variable 1 = yes then create a task update variable 1 sent = yes variable 1 date = execution time can i use the create assembly workflow to add x number of days to variable 1 date and show in variable 2 date. any advice would be helpful please. thanks heaps regards Ridhima If Variable 1 Date is a DateTime field in CRM, then yes. You can use the Add DateTime assembly to take the Value in the DateTime field (which would be t...

Formula #2
I am trying ot put in a formula. I have a two columns with Revenue listed. One is the Estimate, one is the actual. If there is not an amount in the Actual amount column, I added the cell that was in the Estimate cell. I color coded the cell because I am breakingit out by Sales Person. I have 3 Sales People. I went in and did the SUM and went into each cell that is colored coded to each person. I am getting an error. Please help. Thanks, Vanessa Not enough information to more than guess at what you might be doing wrong. How are you going into SUM? Are you clicking on the sum icon? ...

Formula question 03-03-10
I am trying to complete the following. In s141 i have a drop list with 'yes' and 'no' as possiblities. In x141 i have a drop list with 3 possible choices, .02 , .04 , or .08 g141 is the originating cell p159 is the answer cell. if s141 remains empty or has 'no' chosen from the drop list i need p159 to remain blank, however if 'yes' is in s141 then i need the answer from g141 multiplied by .02 or .04 or .08 (which ever one is chosen from the drop list in x141) to be displayed in p159. Can anybody help? Thanks in advance. Scoob...

Formula involving different sheets
Hi Using Excel 2003. In sheet 1, cell A1: =200/1200 In sheet 2, cell B2: =300/1200 In sheet 3 in a cell, I want to do =200/1200 + 300/1200 by cell reference. How do I do so? Thanks. Your formula would be: =sheet1!a1+sheet2!b2 The best way to create these formulae is to get Excel to do it. 1: Type = into your cell 2: Navigate to your sheet1!a1 cell. You will notice in the formula bar that Excel is adding its address to your formula 3: Type + 4: Navigate to your sheet2!b2 cell. 5: Hit enter Once you learn this, you will never type a cell address again. Regar...

Changing a formula's result to text
I have a pretty simple worksheet that lists the date in column A. In column B, I have a formula that converts the date in Column A to the proper day of week. When I put it in a pivot table, it treats column B as an individual date, not the proper DOW. I attempted to copy/paste special/values into a new column, but it doesn't change it to the DOW, it leaves it as the date. I am trying to get Excel to permanently change the cells in column B to say "Tuesday" instead of 4/19/07. Does that make sense? All of the conventional answers don't seem to work for me. Any help? It...

How to execute a parameterized Sql store procedure within Excel?
How to execute a parameterized Sql store procedure within Excel and pass the Excel cell value as the parameter(s). Need to look at sample to understand the concept, if any. Many thanks. hi, Paul ! > How to execute a parameterized Sql store procedure within Excel and pass the Excel cell value as the parameter(s). > Need to look at sample to understand the concept, if any. Many thanks. see if any of the following is what you want... Ed Ferrero: Run SQL Server stored procedure in Excel macro -> http://tinyurl.com/pdmg2 Robin Hammond: Stored procedures -> http://tinyur...

Adding something to a complicated formula
Hey peeps! I have a formula that turns the cell red if the number of received items is less than the number ordered, and only if the scheduled date they were to come in is past today's date. I would like to add in the formula the ability to recognize in the ordered and recieved column if I needed to order more at a later date, or if I received the total items at different times...like 11+3 in the ordered column, meaning I ordered 11, realized I needed more and ordered 3 more. Can I make a formula to recognize this additional requirement? Here is the original formula I have in the cell...

Formula Result Won't Query
Hi All, I have a range of cells and some of them contain this formula: =IF(\$C\$1="Glamour","15.688",IF(\$C\$1="Econo","15.563","Select Style")). The problem I have is when I query this range on another work sheet, it leaves the results of any cells with this formula blank. It is important to me to have this formula in the cell, do you have any suggestions. Sincerely, Nathan Sargeant -- natei6 ------------------------------------------------------------------------ natei6's Profile: http://www.excelforum.com/member.php?action=getinfo&a...

Can a workbook be used as a function
Hi, I have an excel workbook (A) that givern three parameters, temp1, temp and flow calculates the energy transfer for a process we conduct, have another workbook (B) which contains the hourly logged data for th process from which we generate monthly reports. Is it possible to use workbook (A) like a function and pass to it th three parameters from workbook (B) and return to workbook (B) th answer ie. energy transfered. Workbook (B) contain approx 750 logs so i need to repeat th calculation 750 times I could link the two which works for the first piece of logged data bu how to automatically...

Please explain the steps to use the Dlookup function in Access?
explain me the steps to use the dlookup function like how we use in EXCEL See: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Balu" <Balu@discussions.microsoft.com> wrote in message news:00601393-C86C-47BD-9103-52D0FAA292C9@microsoft.com... > explain me the steps to use the dlookup function like how we use in EXCEL ...

function to convert 'nnnnnnnnnn' ---> 'dd.mm.yy hh:mm:ss'
Hi NG, I am looking for a function of MS EXCEL to convert the seconds from 01.01.1970 (e.g. 1096200072) into a more common date format like dd.mm.yy hh:mm:ss. TIA, Henning Hi =109620072/(24*60*60) and format in some time format as you need -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "kielhd" <kielhd@freenet.de> wrote in message news:10a4173d.0409290327.5d18681b@posting.google.com... > Hi NG, > > I am looking for a function of MS EXCEL to convert the seconds from > 01.01.1970 (e.g. 1096200072) into a more common date format...

Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...

more formula help
Once again working on the fundraiser spreadsheet... Teacher wants ... of total money raised, 40% is profit...of that profit the first \$15 goes to the general account, the difference goes in the student account. The only thing I really need to keep track of is the student account, which I figured would be (cell*.4)-15, but the problem I have is ... If the child raised zero dollars, it's showing a -\$15 dollar balance, but I would need it to show zero. Any help is appreciated. Vivian K =MAX(yourexpression,0) -- Gary''s Student "Viviank" wrote: > Once again wo...

new user with a formula problems
Im new at all this and Im not sure if Im explaining this correctly, bu im trying to make a bowling league summary sheet which can tell th players what thier high game is after each week. I tryed to us th what if formula, but it just gives counts. I want a number total. ho do i get started with this. the games for each player has 33 columns. Is this a what IF formula? this was the formula i was using and all i does is count not give me the "number" in which was bowled. =COUNTIF(G10:G42,">175" -- Message posted from http://www.ExcelForum.com Hi have a look at SUMIF...

Formula Help... #2
I apologise in advance if I am not able to explain exactly what I want to do with this formula, but here goes.... I have a formula that references 2 cells on a separate worksheet based on the entries in a number of other cells, and then divides these two values - The formula is shown below. Basically it is looking up Sales data using one of the ranges, and Stock levels using the other range in order to calculate the number of weeks of stock cover. =VLOOKUP(\$B3,INDIRECT("'"&C\$2&"'!\$F\$300:\$BF\$492"),MATCH(\$B\$1,INDIRECT("'"&C\$2&"...

How to print 2000+ characters seen in formula bar? #2
Formula bar shows +1024 characters, screen only shows 1024. Printer only prints 1024. How can I print all? Add some alt-enters to force new lines within the cell. Use a smaller font. Widen the column width lengthen the row height. Curious wrote: > > Formula bar shows +1024 characters, screen only shows 1024. Printer only > prints 1024. How can I print all? -- Dave Peterson ec35720@netscape.com Thanks, but none of your suggestions work. I'm using Excel 2003 by the way. "Dave Peterson" wrote: > Add some alt-enters to force new lines within the cell. &g...

Formula Question #16
I'm trying to total numbers that match certain cells as in the following example: A B C 26 x 34 y -117 z 120 z 2 x 14 y 39 z I want column C to reflect the running total for A as it relates to Z. So column C would read: C 26 26 -91 29 29 68 Hope I explained it well. Thanks I'm not sure why C1=26, since it's an x value not a z value... In general I'd say put this in C1: =SUMIF(B\$1:B1,"x",A\$1:A1) and copy down. In article <ik2n631hd76ull2o199i5ugmpg4201i53i@4ax.com>, Crystal <nospam@nospam.com> wrote: > I...

what's the function key for highlight
I've searched and searched and can't find it. All I want to know is what keyboard shortcut do you press to highligh the selected text so you don't have to press the button on th toolbar. Can anyone help? Plus if you know of a link to a great list of ALL Excel shortcuts tha would be great. Thanks -- Message posted from http://www.ExcelForum.com Hi phipywr! Here's three links: Chip Pearson: http://www.cpearson.com/excel/ShortCuts.htm Dave McRitchie: http://www.mvps.org/dmcritchie/excel/shortx2k.htm#calc And try using Help. Search on Keyboard Shortcuts. I don't kno...