Hi

I am trying to tie one cell into a formula that is being pasted into multiple rows.

I know you can use $ to keep the cell consistent but it doesn't seem to be working.

So if the value is in c9 and then first row gets c9 then the new row gets c10 etc... I need it to be c9 throughout the column.

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link __ http://downloadreimage.com/download.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

You use $C9 to keep the column consistent, C$9 to keep the row consistent and $C$9 to keep the cell consistent. Are you doing that and it's not working?

Read other 2 answersFor some reason I cannot get Excel 2000 to display the results of a formula. Only the formula text displays. I've gone to Tools-Options-View-Windows Options and checked and unchecked the Formulas checkbox. It doesn't make any difference.

Help!

Thanks,

Thomas

Format the cell as a number, it prolly is fmt'd as text

Hi ~

I'm working on a financial formula, trying to calculate a growth rate for a row of cells. The problem is that not all rows have complete data sets, so the formula picks up blank cells and returns a #DIV/0! error.

The existing formula works fine for those rows that have non-zero entries in all cells, and I *can* manually adjust the formula for the starting / ending cells in each row--but what a pain(!). As this spreadsheet grows larger, or the data sets change over time, it means I have to constantly keep adjusting it for every row of data involved, which is extremely time-consuming.

Here's an short example of what I might have in any given row:

A1 (blank), B1(blank), C1(blank), D1(.10), E1(.25), F1(.72), G1(1.0), H1(.80), I1(.60), J1(.75)--then in K1 is the growth formula, e.g. =((J1/A1)^(1/10)-1).

Is there ANY way to create a formula that will dynamically adjust for the blank cells that might exist across a row of data and let the formula do the calculation?

I'm new to advanced Excel topics, but any ideas would be MOST appreciated(!).

Thanks -

CP

If it's possible to do this, what is the formula syntax for an if/then formula in one sheet that refers to the cell value in another sheet in the same workbook?

For example in Sheet 2, the formula would be something like, if Cell A10 in Sheet 1 is greater than 0, then the value of Cell B20 in Sheet 2 would by "Yes" but it would be "No" if the valuein Cell A10 in Sheet 1 was less than 0.

Hi, I need to do the following:

Search array

1 a 12 asdas

2 b 121 agrsfsa

3 c 3 qwgds

4 b 131 erwer

5 e 4123 q3eqt

need to return something like this

lookup value b

answer should look like this

2 b 121 agrsfsa

4 b 131 erwer

index, match can give me one row, however what could return multiple values

Thanks in advance for your help.

Cheers

Read other 8 answers

I have created a formula in Excel that works fine, but when I put it into a MS Word table, it doesn't seem to work. The formula adds up a column of prices when the resulting column has a specific word in.

The formula is:

=SUMIF(E:E, "Yes", D:D)

Example:

Is this possible to do? If so can anyone help converting this so it will work in MS Word.

Thanks, Adam.

You'll have to do this:

https://support.office.com/en-us/art...4-0d7e88062257

Hi,

I am trying to pull a dashboard view from a "Tracker". Given below is the formula that I used:

=COUNTIFS('KT Tracker'!$T$2:$T$138,$C$2,'Tracker'!$U$2:$U$138,"<="&TODAY()+7,'Tracker'!$L$2:$L$138,'Monthly Dashboard'!$B17,'Tracker'!$AF$2:$AF$138,'Monthly Dashboard'!$A$13,'Tracker'!$AD$2:$AD$138,"<>Cancelled")

This perfectly works fine in my laptop that has excel 2013 but when I email this to my team who have Excel 2010, they only see zeros. We have checked all options like enabling Automatic Calulcation of Formula, removing all named ranges in the sheet etc., but it is just not working.

Can you please help ? This is a bit urgent and I need to present this for a meeting tomorrow.

Appreciate your help and thanks in advance !!!

It might be something to do with "<>cancelled"

I cannot search for data in excel 2013. This is new issue. I do notice that in the find window there is an entry for Preview* in the box next to format in the options window that I have never seen before. I get the message cannot find what you are looking for but I know it is there because I am looking at it.

This is new issue. Worked until last night when I was doing a sort and had to make all merged cells uniform in size. Now I can sort but cannot find.

Have closed and reopened excel, but it did not correct problem.

I am trying to create a formula in excel with great difficulty.

The files has 3 tabs. I am trying to take from the first tab called tracking. Here is what I need:

count if d3:d999="Ace" and e3:e999="Damage"

Now, it won't seem to do both functions. I only need the count if both are true within that range, so if D5="Ace" and E5 ="Damage" then count 1, but if both aren't true for the range, then not to count.

Any help?

irishgal said:

I am trying to create a formula in excel with great difficulty.

The files has 3 tabs. I am trying to take from the first tab called tracking. Here is what I need:

count if d3:d999="Ace" and e3:e999="Damage"

Now, it won't seem to do both functions. I only need the count if both are true within that range, so if D5="Ace" and E5 ="Damage" then count 1, but if both aren't true for the range, then not to count.

Any help?Click to expand...

Try,

=SUMPRODUCT(--(D3 : D999="Ace"),--(E3:E999="Damage"))

i have a time sheet im putting together and i need help:

If cell A8 = a number other then 99 put the Letter "M" in cell E8

but if cell A8 = 99 put 99 in E8

but if cell A8 is blank put "A" in E8

thank in advance

The formula you need in M8 is

=IF(E8=99,99,IF(A8="","A","M"))Click to expand...

Then copy that down.

Also if you custom format the date as

dddd d/m/yyyy Click to expand...

Excel will include the day for you automatically.

I am trying to design a form that will automatically search my excel sheet and extract and display rows of data that meet the criteria.

The data I have in my database contains numerous field with address and sales info, but the fields that I need to sort are by Name, date and price range.

On my input form I want to be able to specify a customers name (contained in the name field of my database - column A), I want to get sales within the last 90 days (from the date field in my database - column H and sales value with a certain range......say within $100 of an amount I specify, this would come from the sale value field - column F). I also only want the last 5 sales if there are more that fit within that date and price criteria.

Is there a formula that will seach my database, filter the columns by the above criteria and then display those data rows on my form......in cells/rows that I specify?

Currently I am manually filtering the fields to get the criteria and then copying and pasting onto the form. Any help would be greatly appreciated.

Haven't got the time to help, but I seriously suggest that you read up on advanced filter

http://www.meadinkent.co.uk/xlfilter.htm

I have put the link to Excel 2003, but all the Excel versions have Advanced Filter. Excel 2007 has it as well.

I use Excel 2007 and each time I open an Excel file it seems the formula bar is hidden. Is there a way to have the formula bar open by default? Also, I like 2007 versus 2003 but one thing I miss from 2003 is the help entry field in the upper right corner of the screen. Is there a way to use that in 2007 also as opposed to bringing up the help window with F1?

thanks

dennis

Hello all. This relates to a sports league. I currently have a formula that calculates player performance based on points given for certain achievements divided by number of games played. Essentially its

((# of Goals * 2) + # of assists) / Games played

What Im trying to do is add one more parameter that weights a players score higher (or lower) depending on the number of games played (so more games played increases the score relative to another players score who has played fewer games). Hope that makes sense. Thanks in advance!

Looking for help with 2 excel formulas with a sheet of text vaules... one formula to compare two columns and give results in a 3rd column less all rows that contain a certain value such as the word "house"? The other formula help would compare two columns for exact duplicate rows and remove them from results in a newly created 3rd row. Any help would be greatly appreciated.

Hi,

I am new to this and very inexperienced in Microsoft Excel. I am trying to create a formula that would do the following. I am using Office 2003 professional and do not even know if this is possible.

IF cell C14 equals more than 1395000 but less than 1520000 then multiply cell C14 by 25%

If that can be done and anyone knows how to do it, I would be extremely grateful for the answer.

Thanks

Welcome to the board.

=IF(AND(C14>1395000,C14<1520000),C14*0.25,"False")

(you don't specify what should happen if C14 is outside the range )

I have a column with the following data examples in it:

8

HQ2394

HL3

H

CDHLQKN3428

HQ234

CDNQ3589

HLQKN342

HQKN342

HKLQ234

HQ28

HL

H8

D98

CDQKN3429

LQKN34289

3

3

3

3

DQKN3429L

KN34

LQKN34289

LKN348AU167RWX

L

L

L

L

L

AU16RWXKNO34

AU16RWXKNO34

What kind of formula can be written so that I can identify any cell with an L, or a 7 , or a 8 in it whether they are the only character in a cell or within a set of characters?

Need help with a GOTO formula... I am trying to do an IF statement that will then goto a particular cell. I.e. IF a1 = checking, then GOTO e1, IF a1=savings, then GOTO e5

If anyone could help with this it would be greatly appreciated.

What I want to do is count columns that contain text entries, and do a running total,so if i have a 100 names or any text in the columns it will count them for me, if i add or delete an entry it will adjust the figures accordingly

Thanks

Rama

I am not very proficient with Excel and need formula help. I am putting together a spreadsheet for a party. It shows names of people, # in party (i.e.2 if it is a couple) and whether they are attending (Y or N). Is it possible to write a formula that shows that if they are not attending, then the # in party is 0? Would also apply to their kids attending. Thanks in advance

Hi,

I need some urgent help regarding the IF formula.

I have a master sheet, from which i want to search data into a second sheet if certain conditions apply.

The master sheet - is the sheet where all the data is input with project numbers, and from there each project has its own page, unto which the data that coincide with each project number is supposed to be transfered.

I have used the following formula:

=IF(Sheet1!$D$9:$D$112=B2,Sheet1!$B$9:$B$112,)

The formula works - but i have the problem that if D9 is the right project number, but then line D10 is another project number, then on sheet 2, the line D10 is empty.

I like the individual project sheets to be filled up on each line, so that there are no spaces in between.

Does my question make sense??

Thanks for any help on this!

C

If your data is all contained in one range (no blank rows or columns in the master sheet), you should be looking at Data,Auto Filter.

It is very fast to filter your data to see what you need and if necessary you can copy the results to another sheet, whilst leaving the master sheet intact with all the data.

Have a read of this. http://www.contextures.com/xlautofilter01.html

need help with a formula... I want the results to be in column AA

If V6 is blank I want the text in aa to read Booking? , if V6 has a date in it I want the text in aa to read Sailed? , if B1-V6>4 I want the text in aa to read Sailed??, if B1-V6>7 I want the text in aa to read Documents?

I have been trying to get this to work and I am not sure if it can be done

Thanks

Chris

Need help in creating a formula to do the following: I need a column to show all deposits made and also a way to show a running balance whenever a entry is made in column (F) to subtract from total balance available no matter which entry is made in column (F) i.e amt paid in row 5 subtracts from column H then entry in row 21 is made that subtracts from balance then entry in row 9 subtracts etc,etc, .... would appreciate any help you can offer.

I running XP with excel 2000. .Thanks Dennis

i have a very simple formula in an excel page which is the product of 3 columns. Currently I have copied it right down the page but it leaves a '0' in each cell. is there a way that the formula will be applied to a whole column without being seen in the unused cells.

I know i can ctrl+d when i put in a new row of figures but wondered if there was another way?

thanks

I am running Excel 2007, and I am trying to figure out how to write a formula for my payroll worksheet. I have attached a portion of my spread sheet. What I want to do is have L28 be just overtime, which is any time over 40 hours. If the RT is less than 40, I want this to read 0. I would also like for K28 to be calculated to be 40 hours, if more than 40 hours have been worked in that week. So if it is less than 40, it should read what that time is. Does that make sense? Any help would be greatly appreciated!

Thank you in advance.

Chris

I have a spreadsheet which has results in columns.

Column A has the name of runners in the event, column B has where they finished (WON, 2nd, 3rd, etc). In column X I want to list the winner of the event for all runners except the winner.

Is there a formula that will do this for me.

The formula would need to be something like:

If column B =WON, then leave column X blank, if column B=(anything other than WON) find next record above where column B=WON and paste column A of that record into column X of this record.

I know that is not very well explained, but i have lots of records of races usually with 8 runners in each and against all that didnt win, I want to put the winners name in column X.

This is probably very simple and I have got it working but it isn't working how I want it to work.

I have created an Excel document to show my monthly payments of a bank loan. I have created 3 columns in the first column is the payment number, in the second column is the amount that the loan is after I have made a payment, and in the third column I want to know how much the loan has been reduced by after the bank has taken interest off.

I have just put this formula in the 3rd column, =SUM(D4-D5), and then dragged this formula down into all the other rows I want this to work in which it does. The problem I am having with this is that the number from the 2nd column get placed in the 3rd row, below the amount that has been reduced, and I would like it to stay at ?0.00 as it looks better.

Here is what I mean

If you notice the number I enter in the balance column appears in the reduced by column, and I don't want it too because it looks very unprofessional.

Can someone please help me out with this please.

Try using IF ISBLANK.

So, the formula could be something like

=IF(ISBLANK(D5),"",SUM(D4-D5))

i am trying to create a formula to multiply one cell with annother at the moment I have in cell H13 ='P+L'!H8

now I need to times this by cell d3 but everytime I enter a value in D3 it shows a nil value in H13

so H13 has an original value of 1285.9 ( a a result of the formula ='P+L'!H8)

Then is there is a value in D3 I want to times H13 by this value

so in D3 I have a value of 0.65 so the value in H13 should read 835.8

if no value in D3 then the value in H13 should stay at 1285.9

Cannot get it to work

Hey all,

I'm new here, so this may be a simple fix...

anyways, I was wondering how to adjust an excel document to expand a formula when you add a row?

Currently, I have used the COUNTA formula to count the number of occurrences in some cells. In the future I know that there are going to be new rows added. Now these won't be counted but they need to be...

Is there anyway to extend the COUNTA formula automatically?

A little background on the list.

So there are 14 names. And then six column headings with various topics. One of which is "Pay Period Missed" I need to be able to count the number of pay periods missed, per name (not all of the names have the same amount of pay periods missed). Currently I use the COUNTA formula, but this will become tricky once more Pay Periods are added next to the name...

Hi, welcome to the forum.

I think posting a sample file without sensitive data will help to explain what you want and it's good practice to also mention the version of Excel you're using.

I guess that what you want can be solved by means of a formula, but it's also possible that a macro will make it even simpler since it overcomes the problem of formula's being deleted by accident.

Hi,

I want to be able to get excel to return a total based on adjacent cells with the returned formula being the lowest number in the other cells.

i.e. cell A1 will display the lowest number of cells A2 to A20 which will all have different numbers

Is this possible? If so can someone give me the formula

thanks in advance

Lee_1133

I attached a picture of the spreadsheet/current formula. I would like to add another formula into cell K101 so that when a value is entered into M101, K101 goes to 0.

Hi all,

I am trying to make a Time Card that lists the amount of hours worked each day in the 15 min interval format but my total on the end is not adding up. Can somebody give me a code that might work for me in the below worksheet?

This is what one row of my time sheet looks like:

Regular Day - Hours Worked 7.45 7.45 7.45 7.45 7.45 #VALUE!

But I want the end value to read 38.45 but instead it is adding them as 100 not 60 (shows as 37.25). I am using Excel 2003.

I hope this makes sense

Thanks so much

Suzzy

Cells A1:A10 contains a list of leap years

Cell B1 is an input cell

Cell C1 contains the formula.

Formula = when a value is entered in cell B1, check if that value is in the range A1:A10. If it is, return a number value of 366. If not, return a number value of 365.

Thanks,

T

Hi tech support guy,

i need to modify this formula =IF(NOW()>=J3,"Create PO", "Future Vessel").

J3 is date(date can be any day).if its today formula will change as "create PO". but i need to change

If before 24hrs of today it has to come as "create PO" otherwise its has to come "future vessel".

Regards

Prem

I'd like to create a formula (some kind of IF statement) to test a range of cells to determine if any hidden rows contain values. The statement could return a statement like "Alert - hidden values" if it returns true.

We have a billing sheet, and some of the users tend to hide rows that don't apply, forgetting to clear the values first. Then, the total does not appear to calculate correctly.

Any ideas? I know how to do the if statement, I just don't know how to test the range of cells in a formula. Thanks.

Helllo,

I need to connect a sheet that shows my grades: percentages, and number of points the project is worth, and i need to connect a sheet that shows the grading scale, it looks like this..

100 A+

99 A+

98 A+

97 A

96 A

95 A

94 A-

93 A-

92 B+

91 B+

90 B+

89 B

88 B

87 B

86 B-

85 B-

84 C+

83 C+

82 C+

81 C

80 C

79 C

78 C-

77 C-

76 D+

75 D+

74 D

73 D

72 D

71 D-

70 D-

69 F

So i need to somehow put a formula into a cell that says because i got 100% on this project, (then it connects with the other sheet) it is an A+

Please Help! thank you in advance

Description:

If cell A2=US or if cell A2=CA then return NALO, if not then return INTL. I have the following formulas and just need to merge using the OR statement I think?

=If(A2="US", "NALO", "INTL")

=If(A2="CA", "NALO","INTL")

Thanks,

Ben

I need some help coming up with a formula. Here is what my excel sheet looks like.

Column A Column B Total

45 15 70

10 6

15 8

45 35

6 18

45 20

I need a formula that will search for all instances of the number 45 in column A and when finding that value I need to sum their corresponding amounts in column B. I want to be able to plug numbers into column A and B and have it sum the amounts automatically. Can anybody help me out?

Like this?

=IF(A2=45,(A2+B2),"")

Gives:

Column A Column B Total

45 15 60

10 6

10 8

45 35 80

6 18

45 20 65

I need to know what the formula is to get a percentage between 2 cells ie

I add cells a1 through a10 I total in a11. A12 is a goal number. How can I get the percetage of a11 in respect to a12 to show up in a13.

Hope this is clear enough.

thanks

in a13, type =a11\a12, then format a13 to show as a percentage.

i have the following formula

=if(c4="agy","",if(d4="ot",if(count(f4,g4)=2,g4-f4,)*24-.75,"",if(e4<=10,(h4-e4),"",))

and i keep getting an error some where in the last part of it, as everything that i want it to do in the first if statements are working ok, but the last one gives me an error box saying the formula you typed contains an error, can any one see where the error is

TIA

i need a bit more help with my time sheet

i have posted it here: http://www.ijnw27094.pwp.blueyonder.co.uk/wagesmast.xls

the problem that i am now having is that if some one is at work on an overtime shift i need to show the full time on shift as over time, i have inserted another col that if i put ot in ther what is the if statement that i will use to get it to just show in the overtime col and not in total hours, or can i use an or statement as in if(c4="agy",if(c4="ot", and then what else would i need to do

TIA

Tech Support Guy System Info Utility version 1.0.0.4

OS Version: Microsoft Windows 10 Home, 64 bit

Processor: AMD E-300 APU with Radeon(tm) HD Graphics, AMD64 Family 20 Model 2 Stepping 0

Processor Count: 2

RAM: 3682 Mb

Graphics Card: AMD Radeon HD 6310 Graphics, 384 Mb

Hard Drives: C: 273 GB (183 GB Free); D: 23 GB (2 GB Free);

Motherboard: Hewlett-Packard, 188B

Antivirus: Windows Defender, Disabled

Hey Folks, it has been about 20 years since I wrote formulas in excel and I just do not remember.

Wanting to set up a formula so that the sum of column G cells are always equal to 1000. I need to subtract the value of the corresponding cell in column E. For example if E45 equals 500 cell G45 needs to equal 1000. Greatly appreciate helping an OLD user.

not sure i understand

column g always = 1000

why sum

can you

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone

We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

I have a list of figures, inlcuding minus figures and I am having difficulty coming up with a formula to do what I want to do. What I want is to have a formula, which checks a column of figures "A" and if an amount is 0 or negative, then put the amount in a new column, "B".

Thanks in advance.

Hi Fergus,

In Column B (cell B1 in this example), type:

=IF(A1<=0,A1,"")

The double quotes is just to ensure nothing is written to cell B1 if A1 is above zero.

HTH

Gram

dear friends

is there any formula who prints the exect sheet name

eg.

in my excel workbook the 1st sheet name is "sample" so i want to use a formula who shows the sheet name as it is

I am trying to create a formula that will do the following:

IF(H3="M" or "R", "Done")

I don't know how to tell excel I want the statement to be true if H3 is equal to M or R without creating two if statements. Any ideas?

Thanks

try this

=IF(OR(A6="M",A6="H"),"Done",0)

If you Don't wish it to show 0 for none M or H values, go to Tools | Options | View Tab, uncheck Zero Values in the Window Options section

Hello,

Just wondering if anyone can help with probably simple formula, I'm just not that good on excel.

I have a spreadsheet that in the first tab (call it x) has 2 colums for example name and phone number.

On the second tab (call it Y) it also has 2 colums for example name and DOB.

I would like to make a formula on colum 3, on X, that if the name matches the name on Y it will show the DOB.

Is this possible

Thanks

Look into the vlookup function. V stands for vertical. So basically it looks vertically through a list. This is the format of it.

=vlookup(value to lookup,where to lookup the value, what column should be the end result, set this last section to false for your lookup since you want to look for an exact value isntead of a range)

I hope you can help me with a fairly simple formula, but if formulas are really hard for me.

I want to put the work PAID in Cell b if Cell c has a date in it, otherwise I want it blank. Thanks in advance for your help.