IF/OR formula

I have this formula in a cell:

=IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a")

I also want to apply this formula to the same cell:

=IF(F71="XL","--","")

Both formulas work independently, but the first one is for if NUMBERS
are entered in column F and the second formula is for if specific TEXT
is entered in column F. How could I combine these two formulas? I think
I need to have the formula first identify the type of contents in column
F in order for this to work...?

Thanks.

0
hmm4023 (43)
12/13/2003 9:11:15 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
408 Views

Similar Articles

[PageSpeed] 48

Would
=IF(ISNUMBER(H71),IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a"),IF(F71="XL","--"
,""))

-- 
Regards,
Auk  Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"hmm" <hmm@hmm.com> wrote in message news:3FDAD812.A8F7439D@hmm.com...
> I have this formula in a cell:
>
> =IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a")
>
> I also want to apply this formula to the same cell:
>
> =IF(F71="XL","--","")
>
> Both formulas work independently, but the first one is for if NUMBERS
> are entered in column F and the second formula is for if specific TEXT
> is entered in column F. How could I combine these two formulas? I think
> I need to have the formula first identify the type of contents in column
> F in order for this to work...?
>
> Thanks.
>


0
12/13/2003 9:56:28 AM
Hi,

Try:
 =IF(F71="XL","--",IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a"))
or
=IF(H71>0.99,(K71/RIGHT(F71,3))/H71,IF(F71="XL","--",""))
or
=IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a")&IF(F71="XL","--","")
depending on how you want the ned result to appear. Try each version to see
which one give the results you want.

Cheers

"hmm" <hmm@hmm.com> wrote in message news:3FDAD812.A8F7439D@hmm.com...
> I have this formula in a cell:
>
> =IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a")
>
> I also want to apply this formula to the same cell:
>
> =IF(F71="XL","--","")
>
> Both formulas work independently, but the first one is for if NUMBERS
> are entered in column F and the second formula is for if specific TEXT
> is entered in column F. How could I combine these two formulas? I think
> I need to have the formula first identify the type of contents in column
> F in order for this to work...?
>
> Thanks.
>


0
12/13/2003 10:05:22 AM
Use ISNUMBER or ISTEXT to determine the format of the cell.  So, something
like:

=IF(ISTEXT(F71),IF(F71="XL","--",""),IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a
"))

Regards

Trevor


"hmm" <hmm@hmm.com> wrote in message news:3FDAD812.A8F7439D@hmm.com...
> I have this formula in a cell:
>
> =IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a")
>
> I also want to apply this formula to the same cell:
>
> =IF(F71="XL","--","")
>
> Both formulas work independently, but the first one is for if NUMBERS
> are entered in column F and the second formula is for if specific TEXT
> is entered in column F. How could I combine these two formulas? I think
> I need to have the formula first identify the type of contents in column
> F in order for this to work...?
>
> Thanks.
>


0
Trevor9259 (673)
12/13/2003 10:08:41 AM
Thanks everyone for taking the time to respond. Y'know, newsgroups are the best
part of the internet , the way it was meant to be used-- these groups really
balance out all the pop-up adds, viruses, porn emails and other crap the web
has brought into our lives!

Anyway, macropod's first formula was the one that worked under all conditions:
=IF(F71="XL","--",IF(H71>0.99,(K71/RIGHT(F71,3))/H71,"n/a"))

Thanks again for the help, everyone.

0
hmm4023 (43)
12/13/2003 7:22:36 PM
Reply:

Similar Artilces:

Copying a formulae down a column that includes an INDIRECT
For example, in C1 I have a long formulae that includes multiple INDIRECT references to B1 which is where I input a data figure - column B has data that is dragged and dropped between cells up and down that column (hence the reason for including the indirect) column C provides the answer. After setting up a formulae in C1 how can I copy this down column C and make the indirect move with it - currently the indirect always stays refering to cell B1 ???? =indirect("B"&row()) if that's the formula in C1. A.Webb wrote: > > For example, in C1 I have a long formulae ...

FORMULA #2
Hi I am having difficulty in entering a formula i have created a time sheet and am using TExt to check the differnce between cells / times this is fine but when i leave a cell blank it gives me the error code #value! and this messes up my total box can i change the #value! to be seen as 0 any help will be appreciated Hi Brian Try: =IF(ISERROR(YourFormula),0,YourFormula) -- Best Regards Leo Heuser MVP Excel Followup to newsgroup only, please. "BRIAN" <BRYPOPS@YAHOO.COM> skrev i en meddelelse news:019001c349da$03143520$a001280a@phx.gbl... > Hi > I am having dif...

Formula help #70
I have a column of number in column E, totaled at the bottom of the column. I want to enter something, i.e. “YES”, in column D that will cause the value of column E to be included (or excluded) in the total, based on the entry. Can you help? Maybe try a formula such as the following: =sumif(range, criteria,[sum range]) where, range= col D with "Yes" or "No" or 1 or 0, etc criteria= the logic behind the formula sum range = the range contained in col. E with the values you want to sum. Your formula may look like this: =sumif(D1:D100,"Yes",E1:100) This t...

Autofill formulas when adding rows in tables
Is it possible to have the formulas you are using in a column automatically appear in the new cell in the column when you add a new row? I swear I saw this demonstrated in a video but they did not explain how to do it. I just converted a table of data to an Excel Table (using Format as Table command); took out an old forumla (at top cell of data in column) and then reentered in strcutured reference. I then copied down the entire column. But when I add a new row to the table that new cell in that column does not have the formula. Did I imagine that video or am I missing a st...

Locking Formulas #2
Is there a way to lock a series of selected formulas without going through and editing every individual one? This is very time consuming. I have looked and I haven't found anything thus far. Thanks. What do you mean by lock and why do you have to edit a formula to lock it? You can select the whole spreadsheet, do format>cells>protection and clear locked, then select all the formulas you want to lock and reverse that and select locked, then protect the worksheet -- Regards, Peo Sjoblom <tanner.robin@gmail.com> wrote in message news:c5f3ad46-08f1-4498-bd80-2c73eb6...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

Simple Formula Problem
I enter this formula into C2 ="Y329/"&b2 and In C2 I get the same answer ="Y329/"&b2 In other words for some reason excel is not seeing it as a formula but as a text string. Does anyone know why this is happening? Have I turned on an option accidently? I just enter it by typing directly into the cell "Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message news:%235ucXHIpFHA.3760@TK2MSFTNGP10.phx.gbl... > how do you enter, though VBA? > > Mangesh > > > "bj" <bj@hotmail.com> wrote in message > new...

Formula's
I'm interested in changing excel cells with numbers > 2 to the text "Up" is there an excel formula that could do this? Many Thanks Please don't post more than once, you already received an answer -- Regards, Peo Sjoblom <Patrick.Killela@gmail.com> wrote in message news:448861c0-d8b5-43da-b197-fbcd0036f09d@a1g2000hsb.googlegroups.com... > I'm interested in changing excel cells with numbers > 2 to the text > "Up" is there an excel formula that could do this? > > > Many Thanks ...

Excel Formula Copy
Why would a formula copy down the whole column but NOT provide the correct answers within the cells at one computer and at another computer would work? Jennifer schreef: > Why would a formula copy down the whole column but NOT provide the correct > answers within the cells at one computer and at another computer would work? Could you be more specific? What does it say on the other computer? Maybe in Tools > Options> Calculations "manual" is selected. Maybe the Automatic Calculation option is off on the computer it wont work on?? Thats my only guess "Jennifer&quo...

Formula that displays a Modified Date
Is there a formula that will display the documents "Modified Date" is a cell? Thanks, raisorpr. A UDF '----------------------------------------------------------------- Function DocProps(prop As String) '----------------------------------------------------------------- Application.Volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function and enter in a cell such as =DocProps ("last author") or =DocProps ("last save time") -- HTH...

Annoyign Formulae Problem
My work for the day involves calling our suppliers and getting them t send us their price lists. I then take these price lists and forma them in a certain way and make sure the prices are right. Whil formatting these documents I often run into a problem where I put formula into a cell, and instead of displaying the result it jus displays the formula. Ie. =right(A1,3) <---- displayed in a cell Now I find I can get around this problem my selecting everything, cop it, and paste special (values) to a new book....but this is jus annoying. Is there a preference or setting somewhere th...

Formula which adds together worksheets
Sounds easy - however I am looking for a formula which adds together the same cell across identical worksheets that do not exist. Confused? Well - the template I have set up allows the user to select a country and then run a macro that creates a new worksheet for that country. These worksheets are identical. Once they have selected all the necessary countries the "Total" worksheet needs to have a formula which adds them all up. The problem is that I can't have a normal formula in the Total sheet before the new countries templates have been set up. Any ideas? Check out t...

Formula for conditional "ifs"?
Hi, I have a cell say "C2" containing a dropdown list for "small biz", "corporate" and "enterprise". I have another cell say "C5" that refers to cell "C2". a. If user select "small biz" in C2, C5 will show a price of say $1000. b. If user select "corporate" in C2, C5 will show a price of say $2000. c. If user select "enterprise" in C2, C5 will show a price of say $3000. Now, i need to add another condition to the above. I have another cell A2 for user to enter a "Y" or leave as blank. d....

Result of formula not displayed (rather the formula itself is displayed)
Hopefully a rather straightforward question, but I just don't seem to find an easy fix: I have an XLS where some cells will not display the result of a formula, but rather the formula as such. On other words: if the contents of the cells would be: cell A2: 3 cell A5: 7 if I enter =A2+A5 in another cell, I would expect it to display 10. Yet, some cells display =A2+A5 That same formula does work correctly in most other cells. So I guess this has something to do with the formatting of the cells. Yet, the formatting of the cells is "number", and I have no quotes or blanks preceeding...

Please help with Excel formula problem!
I've spent hours and hours trying to figure this out, with no luck. I'd be PROFOUNDLY grateful if anybody can help. You have several cities, say 10, requesting various amounts of funding. You, the fund source, have a total of $1,500,000 in funds to give them. However, the total amount they're all requesting is, say, $1,655,502. You don't have enough; you only have $1,500,000. 1,500,000/1,655,502 = .9061. So, you grant EACH city 90.61% of what it requested, and all the grants total up to $1,500,000. No problem with that one, you can throw up a little spreadsheet in a few se...

how to prevent a formula in a summary sheet from automatically updating when a row is addedto the referenced sheet
I have a workbook set up that consists of several sheets and a summary sheet that refers to the third row on every other sheet. The worksheets that are referenced contain dated information that is sorted with the most recent entries entered in row 3 and these are the entries I need to track in the summary sheet. When I insert a new row 3 in these worksheets, the formula in the summary automatically updates to line 4 and I need it to always reference row 3, the newest entry. This should be simple but I cannot find the solution. Any ideas? Thanks, krisp1950 You could use the INDIRECT fun...

formulas correct yet not calculating until reopening file
an excel file is not calculating based on programmed formulas unless i close the file save it then reopen. only then the formulas calculate Hi Tennille, Verify that the calculation mode (Tools | Options | Calculation Tab) is set to Automatic. --- Regards, Norman "tennille" <tennille@discussions.microsoft.com> wrote in message news:76F5BE07-D24F-4F12-8DB5-4761931FD8E5@microsoft.com... > an excel file is not calculating based on programmed formulas unless i > close > the file save it then reopen. only then the formulas calculate > ...

Auto Copy Formula????????
I have a list of names from A1 to A10, and letters from B1 to B10. These letters switch rows at random, but within my above range. I need a formula that I could put in C1 that would look in the range B to B10 for a selected letter, and copy a list out of A1 to A10 with th names that corrispond with the selected letter. IE Names---------Letter---------Selected A Name 1-----------A-------------Name 1 Name 2-----------B-------------Name 4 Name 3-----------C Name 4-----------A Name 5-----------B Name 6-----------C I am currently using a filter then copy/paste, but I would love it if had a f...

look up, match index? multi variable formulas
I have a possibility of entering a dollar value in C10 - C14. Once a number is used in C10 it will not be used again in the next months D10, E10 and so on. The same for C11-14. there might not be a number entered in those sells for months to come. IT might be G11 or later on. I set up another cell with the following formula: =IF(C10=0,0,(LOOKUP(C10,Sheet1!$C$7:$C$12,Sheet1!$E$7:$E$12))) This references table that looks like this: Period 1 Periood 2 Period 3 Period 4 Level 1A 1 to 50 $250 0.01% 12.50% 12.50% 20.00% Level 2A 51 to 150 $12,500 0.01% 8.33% 8.33% 8.33% ...

Remove the Formula but leave the value alone?
How easy a macro can be developed to remove the formula but leave the value alone for a group of cells when a special condition matches like my example here? I started with the formula below but realized that I can’t get the result I wanted as to leave the value alone for F4 in the IF formula when the month moving forward. The F4 value originally returned from the Vlookup result. F4: =IF(F2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(F2>B1,C4+D4+E4,F4)) G4: =IF(G2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(G2>B1,D4+E4+F4,G4)) H4: =IF(H2=B1,VLOOKUP($C2,'Sale...

Inserting contents of cell as text into formula using cell referen
I have a spreadsheet for my staff where they log their time against every job they do. Each member of staff has their own work sheet where they enter this data. I am trying to create a summary sheet where I can enter the name of the member of staff (which equates to the worksheet name) to show me how much time they are spending on each job I have on my books. I want to be able to type the name of a person (or worksheet name) into a cell and the various functions in my summary sheet use that text string in that cell as the worksheet reference in the formulas I have entered. for example one ...

Colour references in formulas.
Please can you tell me how to put colour references into a formula. e.g If A1=Fred and B1=Smith then C1= Fred Smith. ("Fred" in red text, "Smith" in green text) Thanking you in anticipation. -- Big Rick Big Rick Wrote: > Please can you tell me how to put colour references into a formula. > e.g > If > A1=Fred and B1=Smith > then > C1= Fred Smith. > ("Fred" in red text, "Smith" in green text) > > Thanking you in anticipation. > -- > Big Rick This cannot be done by a *formula* as you ask. This would take VB coding to ...

I'm new at this
I want F3 = F2 + 5:00 - E3. (The 5:00 is 5 hours and all the numbers are hours and minutes). Maybe it goes without saying, but I would like all the cells following F3 to have a similar formula. For example F4 = F3 + 5:00 - E4. Thanks for any help. In F3 =F2+"5:00"-E3 Copy down as needed. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571 View this thread: http://www.excelforum.com/showthread.php?threadid=498866 Dave... you could just make ...

RoundUP formula
Just need round up results of formula =0.15*E2 is this correct? =ROUNDUP(0.15*E2),2) thanks Parentheses do not match; this will work =ROUNDUP(0.15*E2,2) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Kvin" <kvin@msnews.microsoft.com> wrote in message news:uQLjshe%23GHA.4428@TK2MSFTNGP04.phx.gbl... > Just need round up results of formula =0.15*E2 > > is this correct? > > =ROUNDUP(0.15*E2),2) > > thanks "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message news:OqS46je%23GHA.4356@TK...

Sumproduct formula works in spreadsheet, NOT VBA. Help please
Basically, I want to replace this formula with VBA Code I can get this one to work to sum =SUMPRODUCT(--(DataTime="First day of employment (Time 1)"),--(DataPosition=N6),(DataQuestion1) When I change this to Count rather than Sum, my Code Evaluates as an Error =SUMPRODUCT(--(DataTime="First day of employment (Time 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*")) The only line of code I switch out is the last mFormula row. The line of code that works I've commented out. I am really stumped... This is the cod...