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

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

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.

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.

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 )

Hi,

I am currently working in an excel file where I am having problems with the following formula:

=SUMPRODUCT(--('Forecast Detail'!$E$5:$E$9409='Data Dump'!$B3049),'Forecast Detail'!$J$5:$J$9409)

A little more background on the file I am working with:

- This is a rather large file that I've had to turn calculations on manual so that I am able to work in it without the calculations constantly going

- The data dump cell that it is referencing it to is a concatenation formula combining a cost center and a G/L account. This results in a 18 digit number.

- The forecast detail column E is also a concatenation formula of the same thing.

- The forecast detail column J is the real dollars for the month that goes through the various cost centers and G/L accounts.

I am then trying to pull all of the data that is in the forecast detail sheet that fits the criteria in my formula. However, there is a couple instances where it isn't matching the correct concatenation formula. Does anyone know how I could correct this or if there is a better formula to get the same result accomplished?

Thanks!

Is this an IF condition? (the red text)

=SUMPRODUCT(--('Forecast Detail'!$E$5:$E$9409='Data Dump'!$B3049),'Forecast Detail'!$J$5:$J$9409)

This will return either True or False so I don't see what you're trying

Hi,

I have a table of text values, case sensitive and I tried using COUNTIF() to count the number of occurrences of a particular pair of letters, like so:

BB:RR

BB:Rr

BB:rr

Bb:RR

Bb:Rr

etc....

I did COUNTIF(Range,"*BB*"), but I found it's not case sensitive, and it's counting BB and bb as well. Is there any way to make it case sensitive?

Rgds,

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 am having trouble with working out how to calculate vat @ 17.5% in excel.

I need to work out the vat inclusive price.

can anyone help.

Not sure exactly what you want, but hope this helps:

If £15.00 if the amount including VAT and you want to find what the net amount is (ie excluding VAT) then divide the amount by 117.5%:

+15/117.5% or put in sparate cells and create formula eg +A1/B1

If you want to find how much VAT is on £15.00 then multiple the amount by 117.5%:

+15*117.5% (or in cells would be +A1*B1)

Is this what you wanted?

Have a formula on one sheet looking at another. Need to get it to look at a third sheet if # is 0. Here's the formula.

='R:\STATUS\[GENERATE.XLS]generatetion95'!$G$9

I would like it to look at sheet1 if G9 is 0. Thanks Phil C.

=IF('R:\STATUS\[GENERATE.XLS]generatetion95'!$G$9<>0,R:\STATUS\[GENERATE.XLS]generatetion95'!$G$9,sheet1!CELLREF)

or

=IF('R:\STATUS\[GENERATE.XLS]generatetion95'!$G$9=0,sheet1!CELLREF,'R:\STATUS\[GENERATE.XLS]generatetion95'!$G$9)

where CELLREF is the cell you would like it to reference on sheet1.

IF statements have 3 parts.

the argument (=zero or <>zero)

what to do if true

what to do if false

Hope this helps.

------------------

~dreamboat~

Brainbench MVP for Microsoft Word

Brainbench

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

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

My question is how do I input a formula into Office 2010 Excel that calculates set commission amounts ($100,$150,$200,&$250) for various dollar sales amounts?

I have been able to work it this far:

=IF(D4>2999,D4<4000)*("$200.00")

The part I need to know is how do I expand this formula to include the next level of commission pay off $250.00

How close am I?

=IF(D4>2999,D4<4000)*("$200.00") (D4>=4000)*("$250.00")

I am not sure how I join or add the brackets together.

I appreciate you help.

use nested IFs

so

IF( test , true , false )

now to nest in the false section (you can also nest in true)

IF( test, true, IF(test, true, false) )

if you want to give the details , i can set up

=IF(D4>2999,D4<4000)*("$200.00")Click to expand...

not sure why thats works

I would also look at the structure of the data and you may avoid using the between

but for between use

AND( D4>2999,D4<4000)

as the test

but you can often start with the greatest value

IF( D4 > 4000, "4001+ commision calc", if(d4> 2999, "3000+ commision calc", if( d4>1000, "1001+ commision calc", "value for below or equal 1000")

if you want to include greater or =

>=

the tests are carried out in the order written

so if its false for >4000 - then the next test shows below 4001 but greater than 2999

the word problem is:

1 per 25 for the first 50 and 1 per 50 for the remainder exceding 50

=IF(C11/B11>50,((50/25)+(C11/B11-50)/50),(C11/B11)/25)

above is the formula i have, it is not correct, c11/b11 if the number from which this formula is calculated

the other word problem is:

1 per 40 for the first 80 and 1 per 80 for the remainder exceeding 80

=IF(C11/B11>80,((80/40)+(C11/B11-80)/80),(C11/B11)/40)

above is the formula I have, it is also not correct

c11/b11 if the number from which this formula is calculated.

If i do the math manually i do not get the same answers.

I am really excel illeterate.

Please help, and Thank you!

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.

I need a formula that will lookup between 2 given date ranges and return a sum of numbers in a specific column. For example, I want to sum all dividends for symbol EEF in between the dates of 12/29/2006 and 3/13/2007. I have a sheet set up with all symbols, dates, and dividends that are needed, but i can't figure out a lookup function to only return the values over a given range of time. I hope this makes sense. Thank You

Brandon

I am looking to check and entire row of data up to (200 entries)

For number that is less than one cell and greater then another cell

then enter the value of the cell if yes and skip if no creating a list

I am using excel 2000

Any ideas?

I there a way or formula in Microsoft excel that i can call cells from 1 tab to another?

Need some help on an excel for mac formula. I am trying to make a cost analysis spreadsheet and do not know how to make a formula for the following information:

overnight fee is 240.00 per night

1 to 5 nights with a minimum purchase of 800 gallons-no fee (799 or less-full fee/per night)

6 to 12 nights with a minimum purchase of 1000 to 1500 gallons-half the fee, 1501 or above-no fee (less than 1000- full fee/per night)

12 nights or over with minimum purchase of 1500 to 1999 gallons-half fee, 2000 or more- no fee (less than 1500-full fee/per night)

So far I can get the first one, =IF(AND(B4<=5,E4<800),B2*B4,"0") but after that I am stuck. B2 is the $240.00, B4 is the number of nights and E4 is number of gallons purchased. If there is a better way, I am all ears.

Please make any directions as step by step as possible, as I know very little about excel! Thanks in advance!

I'm working on a long formula that starts with "if" - I can't get it to work -

thanks

This may seem a silly question but can anyone tell me where the equals sign has gone on the Formula Bar that you used to see with Office 97???

Looks like MS deemed it unnecessary. The = sign button was "Edit Formula".

It's been replaced by the Insert Function (formerly known as Paste Function) button. If you want to 'edit a formula', you would just use the Insert Function button.

Additionally, when clicking the Insert Function button, a prefixing = sign automatically appears, defining it as a formula.

If you really wanted, you could add an = symbol to one of your toolbars - right-click toolbar area, Customize, on the Commands tab select Insert in the Categories pane and "=" Sign in the right pane...

Gram

This is a tough formula and I and having alot of problems figuring this out and wondering if any one can assist with this.

I need to format an excel doc giving priority to the column marked DMR, meaning if a number is in the negative in the DMR column I have to back fill the negative number from the IMR column to bring the DMR column to a number of 0, if DMR and IMR are both negative then I can pull from the DDD column to bring the DMR column to a zero. Any number coming from IMR or DDD will then be less the number added to the DMR column.

Also each row of numbers is a half hr period and has to be able to be ran for a day a week or even a month if that is any help but it will always be in this order but the numbers will chng for each day period.

Thank you for any help you can be.

DDD GSL PRD OCS DMR GSS PSS IMR

5 -83 -3 6 -8 3 12

4 -66 4 3 -14 -4 17

4 -45 -2 -8 1 -1 8

6 -31 -1 -5 5 -2 7

-1 -28 -5 -5 2 -1 3

1 -16 -3 -5 0 -5 2

1 -25 0 -4 -1 -2 -1

0 -21 2 -5 7 -3 1

-1 -23 2 -5 6 -2 1

-2 -14 4 -5 4 -2 1

-2 -16 5 -9 9 -2 2

1 -14 3 -6 12 -8 8

4 -17 3 -7 13 6 9

7 -22 3 -10 10 7 11

6 -32 4 -4 13 9 18

16 -41 3 0 17 16 19

14 -61 2 3 7 29 17 17

16 -76 3 5 7 42 28 15

11 -85 -1 0 2 43 32 21

25 -140 -3 -3 2 41 32 26

26 -156 -6 4 6 44 35 27

24 -190 -6 -5 7 62 47 28

28 -200 -9 -7 14 68 47 25

25 -200 -9 -14 14 64 36 28

16 -156 -15 -13 15 67 48 30

26 -150 -11 -12 18 74 34 33

25 -173 -6 -7 19 67 38 28

32 -172 -6 -14 18 99 44 36

28 -179 -11 -16 23 108 40 34

28 -155 -10 -14 27 106 49 33

39 ... Read more

Hi,

I am trying to do some formula to calculate the cell color and the value X.

I have 3 different Team with two different shift, Day (orange) and Night (blue) in the attached Excel.

I managed to sum the colored cell using ASAP formula, but the issue is I need know how many X in day and how many X in night for A,B,C and D column in each day, as in the attachment? what formula should I use?

Many Thanks

I'm trying to use COUNTIF to to count the number of rows in a range that have "CD" as the first 2 characters. I've tried severals combinations along the lines of :

=COUNTIF(LEFT(d2:d1040,2),"CD")

Any ideas ?

Try this one:

=COUNTIF(A:A,"CD*")

It will count any string that starts with CD and contains any alpha or numeric character following it.

This one will count any string that has CD anywhere in the string.

=COUNTIF(A:A,"*CD*")

In an Excel spreadsheet, I want all the numbers to be increased by 1. Can anyone please help with a formula?

Thanks so much 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

I need a spreadsheet formula that will reference an alternate numerical number in a sum function. I have a column of Priority values 1-22 and I want to assign a reverse value to each priority (i.e. 1=22, 2=21, 3=20, etc.) then I want to post the sum of the values incurred and not the sum of the numerical priority values in the cell at the bottom of the column. Should I be using LOOKUP in conjunction with SUM or SUMIF? Any ideas?

Hi guys/gals,

I'm looking for the formula on my spreadsheet to give out a value and if there is no value, display N/A as the output. So for example =E2-D2 is already in the cell and gives me what i want however when there is no value in E2 i would like it to display N/A instead of #Value. The 2 cells for the formula are set up as date/time but occassionally letters may be in these cells instead. Ive probably made that sound complicated. Excel newbie sorry.

Thanks in advance for any replies.

The following formula for exactly what you requested uses the ISBLANK function as the logical_test parameter in an IF function.

Code:

=IF(ISBLANK(E2),"N/A",E2-D2)

A more general approach uses the ISERROR function to return "N/A" if any kind of error is found for E2-D2.

Code:

=IF(ISERROR(E2-D2),"N/A",E2-D2)

Please help!!!!!!!!!!!!!!

A question that have puzzeled me!

excel the total is 55,250.00 and its asking for me to turn this amount for the month of October to a formula equaling 10%.

Can anyone-someone answer this for soonnnnnnnnnnnnnnn.

Ok so I have my products in column B. In column H I have the price of each product. In column P, I want to be able to assign a value to each thing in column B and then add the total per month.

Product Month Price Total Product Sales by Month

Router JAN 899

Monitor JAN 219

Monitor JAN 219

Switch FEB 699

Etc.....Is there a formula for this?

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.

hi,

i was trying to find a solution for a formula in excel.

i have a list of people, each one has a specific date.

i want to count by month the number of people on that list

the result could be on a graphic or cells.

thanx

I'm making an excel sheet and I'm having trouble figuring out the formula to automatically highlight the cell intersection point of a date in a row that equals the same date in a column.

Can anyone help me with the following problem,

I have an Excel 2000 spreadsheet with 7000 rows showing when a user connects or disconnects. The first column shows date in the following format “dd/mm/yyyy” and the second column shows when the user connected in the format “hh:mm” and then the third column shows when they disconnect in the same format. Finally the fourth column shows the length they were connected I need to create a time line for a complete month in intervals of every minute to show how many users are connected at any point in time over that month. . I am going to create the time line across the top of the page but I am getting stuck on the formula to compare the information and report back a valid number. If any one could help me with this It would be greatly appreciated.

Date Start time End time Length

22/04/2003 13:59 14:18 00:19

22/04/2003 14:01 14:18 00:16

28/04/2003 12:07 13:07 01:00

28/04/2003 12:56 13:45 00:49

28/04/2003 12:59 13:45 00:46

28/04/2003 12:59 13:45 00:46

Have I got this right - you want to count how many users are connected for every minute of every day of each month? That's 1440 minutes per day - 44640 in a 31 day month?

Deej, UK

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 with a formula in excel.

I want to be able to calculate surface area, say of a box, by inputting the volume of the box.

I can do it on a calculator, but I don't think excel supports the cube root function.

can anyone help???

Welcome to TSG, Starfish

Cube rooting, although not directly supported, can be done using the POWER formula

=POWER(A1,1/3) (where A1 is the number to be cube rooted)

I am trying to find the formula to total up a sum between certain dates.

for example

In column A i have the dates

In column B i have the amounts paid in

I need to be able to click into a cell and use a formula to calculate the total amount in column B between certain dates in Column A.

EG: the amount paid in between 01/10/2007 and including 30/09/2008

i have tried the following but no sum comes out, =SUMIF(A:A,">01/10/2007and<30/09/2008",B:B)

Please Please can anyone help

Kaly

I am not a very experienced Excel persion but I believe the problem is that the "criteria" argument used in the SumIF function is unable to combine logical expressions like you are trying to do:

Can Do ">6/30/2007" and can do "<11/1/2007"

But Can't do ">6/30/2007 AND <11/1/2007" at the same time

For you particular problem there still is a solution that uses the SumIf function

Add up ALL of the payments and then, using SumIf, subtract off those that fall before the start date and those that fall after then ending date

So to add all the payments between (including) June 1 '07 and Sept 30 '07:

Good:

=sum(B1:Bn) - sumif(A1:An,"<6/1/2007",B1:Bn) - sumif (A1:An,">9/30/2007",B1:Bn)

Now if you think about this approach you see that it isn't really necessary to sum all the payments and subtract off both ends, you only need to sum all the payments that fall on or after the start date and then subtract off those that fall after the end date.

Better:

=sumif(A1:An,">5/31/2007",B1:Bn) - sumif (A1:An,">9/30/2007",B1Bn)

Hi all, bit of a newbie to this forum...

I am after some help finding a formula or vbscript or something that can run through a range of numbers in a column, and give me the highest difference between to cells...

ie...

12

13

17

9

15

18

8

As you can see the difference between 12 & 13 is 1, 13 & 17 is 4, 17 & 9 is 8, 9 & 15 is 6, 15 & 18 is 3, 18 & 8 is 10.

I need a formula that can do this, and in ONE cell, give me the HIGHEST difference. in my example the ONE cell answer would be 10 as 10 was the biggest difference.

IS this possible? if so I would be so grateful of your help.

Many thanks in advance...

Christian.

Hi,

Does anyone know how to formulate the following:

X figure multiplied by .105, then that sum issubtracted from origianl X figure

Is this possible?

Thanks

Welcome to TSG!

=SUM(A1-(A1*0.105))

paste this function in cell B1 when the number you will input is in cell A1

You can then copy cell B1 and paste the function in B2-B??) and the values will change correspondingly to

=SUM(A2-(A2*0.105))

=SUM(A3-(A3*0.105))

etc

i want to use 2 formulas in one cell , 1st i want to find out the actual time between 2 dates and then want to convert that time into hours, can anyone give help please?

is the format something like

dd/mm/yy hh:mm

Then just use =hour ()

and subtract the times

so

=HOUR(A2-A1)

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.

I am running excel 2000 and I need a formula to do what I am fixing to put in if it is possible if it isn't please let me know.

if column c equals the word expense then add column D to Column E and total it below the last row on E if column C equals the word Income then add column D to Column F and total it in the following row of column F

if you can help me with this I would appreciate it ASAP thank you!

Will the attached file work for you? It is set to allow up to 30 entries but can easily extended by copying the formulas into additional rows.