# How To Make MAX Formula Smarter

Jul 25, 2014

I'm currently using this formula to show the last date of a entry ie A15, on a 2 different worksheets, the forst worksheet always has a A15 entry and the 2nd worksheet currentmonth! sometimes has and entry.

=MAX(LOOKUP(99^99,currentmonth!A:A/(currentmonth!B:B=A15),currentmonth!A:A),LOOKUP(99^99,mainlogbook!A:A/(mainlogbook!B:B=A15),mainlogbook!A:A))

and if I don't have and entry in this section of the formula (LOOKUP(99^99,currentmonth!A:A/(currentmonth!B:B=A15) which sometimes I don't.

It yields a #N/A Error.

Is there a way to make this formula work if there is no entry of A15 on the currentmonth! worksheet?

## Make A Formula Look For Something That IS NOT There?

Sep 23, 2008

Total newbie trying to use excel for work here. I have two problems that both need the use of "is NOT in another column". 1. I need to count the number of instances in a column that do NOT have the word "fly" in column G. The current formula is...

## Formula To Make Every 50 Add 1

Feb 24, 2009

If i wanted to tack on something to the end of a formula so that it would make every 50 add 1 (example: 200 would convert to 204, because 200 divided by 50 is 4).

## Use Tickbox To Make Formula Appear?

Apr 5, 2014

It is an example of a list of menu items that I need to put a tick next to whichever item (instead of a 1) in order for the cost of the item to appear in the B column of the Menu Costing Combination sheet. Without the tick the amount does not appear.

How do I go about putting the tick mark in the cell instead of having to put a 1? Of course, the formula in column B will have to be changed in order to replace the number 1.

## Formula To Make A Box A Color

Dec 11, 2007

I need to be able to enter text in a box, and from that box the formula looks at the text to see if it is in within strings of text in any other box in the entire spreadsheet. If the text is there, the box needs to turn green (or any color). If not, it stays the same. The catch is I need the formula to be in a separate box, and the Entered text will be in a blank cell, so the user (who is excel illiterate) will not be confused by the odd formula.

Example:

B1, B2, C2, D4, and D6 will have the items: XXX, YYY, ZZZ
B3 and B4 will have only: YYY

In the "search box" the user will enter XXX

B1, B2, C2, D4, and D6 turn green, while B3 and B4 stay as normal.

If they enter YYY in the search box, all of them turn green.

## How Do You Make Text In To A Formula

Jan 29, 2008

If I have different equations in various cells without an = sign (text only) how do I make the cells into formulas without using the formula bar?

e.g Cell B1 has text in it A1+A2+A3 and we need cell B2 to be =A1+A2+A3

## Make Concatenate A Formula

Feb 24, 2010

I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:

=NeoTicker|q!'@ES#,last'

I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:

G10: '=NeoTicker|q!'
H10: '@ES#

## How To Create Formula To Make Periodic

May 4, 2014

I want to create a formula to make periodic automatically for the next few years. see attachment my sheet.

## Make Formula Output Another Word Again In Next Row Down

Aug 15, 2014

In column A i am using the following formula to output "Our Apple" when "Apple" is in column D and "Our Banana" when "Banana" is in column E:

[Code] .....

I would like the formula to also output "Big Apple" immediately underneath "Our Apple" when its present, and output "Big Banana" immediately below "Our Banana" when it is present.

The yellow column is how column A should look after the formula edit.

Attached File : Book2.xlsxâ€Ž

## Possible To Make A Word In Formula Bold?

Jul 2, 2006

I am trying to make a word within a formula bold.

The word doesn't have to be bolded depending on the result. It will either appear or not based on the result of the formula. But obviously, if it appears, it [should] be bolded...

Obviously you can't format text on the formula bar. This is something I've wanted to do in Excel for YEARS!

## Make Numbers In Formula Stay The Same

Oct 2, 2008

I have a formula:
=INDEX('Part Info'!E1:E300,MATCH(1,('Part Info'!A1:A300=A1)*('Part Info'!B1:B300=B1),0))

I want the E1:E300 to stay E1:E300 when I drag it down but I want the A1 for example to change to A2, A3, A4 and etc.

## Make A Part Number Formula?

Apr 22, 2009

I'll try to keep this brief. Our part #'s look like this. XP014567. Where the first pair of characters stand for a value(say XP=Bolt), the second pair stand for a value(01= Grade 2), and so on. What I would like to be able to do is type in the part numer and have the description automatically populate. Is there an easy way to do this? I'm running Excel 2003.

## How Can I Make A Persons Name Read A Formula

Jul 28, 2009

If I type the name Joe Bloggs How can I make whenever i type that name the computer knows to retreive a number or formula from a cell on another or same sheet.

## Can I Make A Formula That Will Highlight A Cell

Sep 1, 2009

I have a large spreadsheet with many pieces of equipment in it. There are a few piece that I would like to highlight should thier computer license go out of date, or even better 30 days prior. I have a Feild with the Expire dates but it is at the end of the spreadsheet not in plain sight. Is there a way to make my Equipment description column highlight when the date in the other column reaches the current date (or 30 Days before). I have cut and pasted the part of the spreadsheet into a new file to make it easier to read and hid all the none important feilds.

## Formula To Make Cell Blank Instead Of Zero

Oct 29, 2009

I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:
=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")

A B C
10.1810.82

## Make This If Formula Return A Cell Value

Jan 15, 2010

Heres my current formula but its justreturning text i would like it to return the cells value

=IF((J2>K2);"value=J2";"(value=k2)")

## Formula To Make Sure No Cells Are Duplicates

Feb 14, 2008

A1 = 3
A2 = 3
A3 = 3

formula: =A1A2 gives us FALSE....

Why does =A1A2A3 give us TRUE

What is the best formula to use to make sure no cells are duplicates?

## Make A Cell's Values Become A Formula

May 23, 2008

I have on my Sheet named "Data" in Cell K4

=CONCATENATE(L16,O25,L18,O25,K20,S25,N22)

The values of that cell become a formula.

I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.

The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.

Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?

## How To Make + Symbol Variable In The Formula?

Apr 23, 2009

How to make + symbol variable in the below formula?

This formula is in G25 and symbol is in F25 has + & - symbols

=(NOW()-E\$592)+E614

## Formula: Make Drop Down Boxes

Aug 3, 2009

Column A - I want to make drop down boxes #1-30 - which would represent quantity
Column B- That will be 30 rows of dollar amounts
Column C - To be able to calculate the quantity x dollar amount

I'm able to create the drop down boxes with the numbers, but say I choose the number 5 by the dollar amount 200.00 It won't calculate 5 x 200.00....

I've been trying the formula you would use to find an average, and i've tried the if statments, but my drop down box isn't being recognized.

## How Do I Make And SUM(OFFSET Formula Dynamic

Jan 1, 2010

why this isn't working?

=SUM(OFFSET(A4,0,3,1,(LOOKUP(9.99999999999999E+307,4:4))))

## Click To Make Another 100 Rows - Continue Formula Down Too

May 19, 2014

I assume I would need a macro, but I am wanting to have a button where-by the user clicks to create another 100 rows and in-turn, continue the formula's that reside within the columns and rows above, down...

## Psychologist Trying To Make Custom Formula For My Evaluations.

Sep 29, 2007

The test takers are shown three pictures. Under each picture is a number. So for example, let's say there is a plane, a car and a train presented for question #1. Underneath those pictures are the numbers 1, 2, and 3 respectively. Those numbers represent a tendency to a certain occupational field. After 55 questions, we can make a pretty good recommendation of where we think they should look for a job (without going into the specifics of the test).

There are 11 occupational fields. In excel, I make those fields A through K. In field L1 through L55 I put in their answers.

Let's say that in all 55 questions, for example, answering 1, 3, and 5 lead to vocational choice K, I want it to tally it up for me automatically. I don't want to have to count those by hand.

I just want to put in the patients answers, and have excel do the work. I tried using the "countif" function, but it doesn't look like you can add multiple conditions.

I need something that will be like this: add 1 each time (as in a tally) to the cell I choose, for the answers that I specify. So, ultimately, it will be tallying up the answers based on which categories that I correspond them to into the respective cells.

So to be redundant... Let's say that answering 3, 7, and 9 to questions 1, 2, and 3 pertain to cell B. After I put those in, I want cell B to have the number 3 in it.

## Make A Formula Output A Blank Cell?

Mar 20, 2009

I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

## Leadtime :: Make A Substract From A Today Formula

Dec 9, 2009

i got the data in this format and need to get it as date in order to make a substract from a today formula minus that date in that format, if it helps this is data imported from a website.

## Make Formula Based On Two Incremental Values?

Feb 26, 2013

How do I make a formula based on two incremental values

e.g I drag this down

=ROWS(A\$1:\$A1)*B\$4

where B4 is say 0.2

I will get

0.2
0.4
0.6 etc

But what if I want to start from 5 so it will be like this

5.2
5.4
5.6 etc

## Macro / Formula To Make Summary Sheet?

May 18, 2014

I have a work book with 4 sheets in it. Now i want to make a summery sheet for this.

## Make Part Of A Cell Bold Using A Formula??

May 29, 2009

I have a formula that combines the the text of various cells.

Is there a way to automatically make part of the cell Bold - so in this case I want the Cell B13 to appear in bold.

See formula below:

## Make Formula Pick Up Filename From A Different Cell...

Feb 9, 2010

I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!\$I\$32

There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.

## How To Make Same Formula Change In Multiple Places

Jul 7, 2014

I want to change the references in a VLOOKUP formula that is in 20 columns that are not all next to each other. The current formula is:

=VLOOKUP(\$AF5,' COUNTY ROLES '!\$B\$4:\$D\$69,3,0)
and I want the new formula to be
=VLOOKUP(\$AF5,' COUNTY ROLES '!\$B:\$D,3,0).

Is there a way to do this without having to change it in the first cell of each column and then copy/paste it all the way down each column?