Over 1 million tech questions and answers.

Excel 2013 - how to keep a formula consisten

Q: Excel 2013 - how to keep a formula consisten

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.

RELEVANCY SCORE 200
Preferred Solution: Excel 2013 - how to keep a formula consisten

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.)

A: Excel 2013 - how to keep a formula consisten

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 answers
RELEVANCY SCORE 60.8

For 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
 

A:Excel 2000 Formula Text Displays instead of Formula Results

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

Read other 3 answers
RELEVANCY SCORE 60

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
 

A:Excel Formula / How Can I Ignore Blank Cells In Row Formula?

Read other 14 answers
RELEVANCY SCORE 59.2

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.
 

A:Excel formula using formula between sheets in workbook

Read other 6 answers
RELEVANCY SCORE 59.2

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
 

A:Excel: Is there any formula that would do Autofilter's functionality as a formula

Read other 8 answers
RELEVANCY SCORE 56.8

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.

A:Excel Formula to Word Formula

You'll have to do this:

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

Read other 2 answers
RELEVANCY SCORE 54

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.
 

A:Solved: Excel search does not work Excel 2013

Read other 7 answers
RELEVANCY SCORE 54

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 !!!
 

A:COUNTIFS working in Excel 2013 but not in Excel 2010

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

Read other 1 answers
RELEVANCY SCORE 50.4

Hi All,

I have a spreadsheet attached that lists revenue and target by month for each name. I need to make a ytd formula that can calculate this. I cannot move the columns of the data around, so they have to stay in this format. Can anyone help?

Thank you!
Winnie
 

A:ytd formula excel

=SUM(B2,D2,F2,H2,J2,L2,N2,P2,R2,T2,V2,X2)
and then copy across to cell aa

then select those two cells and copy down

or am i missing something else you need
 

Read other 1 answers
RELEVANCY SCORE 50.4

I need help with a formula that would do the following in Excel.

If Column A = Column C2 thru C10, then Column B = D2 thru D10.

C2 would need to match up with D2 and so on. Is this possible and how would I do it. Column A and C would be text and B & D would be numbers.

Thanks
 

A:Excel Formula

Read other 16 answers
RELEVANCY SCORE 50.4

Hi I run a small sales team and need to set up a excel work sheet.
It is for the shift rotas. I need it to calculate hours works.
For example person works 9am to 5pm with half hour lunch. I need it to calculate hours work minus lunch time.Obviously there are various shift times etc any help would be very much appreciated.
Any suggestions...
 

A:Excel formula help please..

This comes to mind, not sure if it is correct. I just put it together
A4 = Start
B4 = End
=ABS((A4*24)-(B4*24))
 

Read other 3 answers
RELEVANCY SCORE 50.4

Hi,

I have an excel sheet that has the words BLANK in one column and Y in another, i need to total the amount of BLANK's as well as the amount of Y's in a formula

~And i am stuck!

Any help would be gratefully received

Thanks
 

A:Excel Formula

Read other 12 answers
RELEVANCY SCORE 50.4

I am trying to make a form that tracks individual employee hours in my workplace. I have a cell (e8) that is total hours worked. Next is cell g8 regular hours worked. Both cells are formatted for time 13:00. I am trying to set up a formula that automatically populates the correct number in the regular hours worked (g8) field. The formula I tried was =IF(e8>=8,8,e8. It always comes up as false and populates the e8 value. I want it to populate 8 hours (not ot, that is totalled in cell i8) unless they worked less than 8 hours, then it should show actual hours worked. Any ideas would be apprciated.
 

A:Excel formula help

Originally posted by hiram33:
I am trying to make a form that tracks individual employee hours in my workplace. I have a cell (e8) that is total hours worked. Next is cell g8 regular hours worked. Both cells are formatted for time 13:00. I am trying to set up a formula that automatically populates the correct number in the regular hours worked (g8) field. The formula I tried was =IF(e8>=8,8,e8. It always comes up as false and populates the e8 value. I want it to populate 8 hours (not ot, that is totalled in cell i8) unless they worked less than 8 hours, then it should show actual hours worked. Any ideas would be apprciated. Click to expand...

Time in Excel are stored as fractions of a day. 8 hours would be 8/24 of 1/3 of a day. Change the 8 in your formula to 1/3 or .33
 

Read other 2 answers
RELEVANCY SCORE 50.4

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!
 

A:Excel Formula Help

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
 

Read other 3 answers
RELEVANCY SCORE 50.4

Hi,

Please look at the sample below:

the following is in Cell O16

"108100 - Metal Mining Services"

I need an Excel formual that will strip all of numeric and strings to the first text character. The numbers have variable amount of digits and the Text will also be variable length. The only thing that is constant id the "-" In this case I am looking just to return "Metal Mining Services", I will need to save the file as a csv afterwards.

Thanks in advance for any help that can be provided.

-Juls
 

A:Need help with Excel 2k7 formula

If the string were located in Cell A1, you could use the code

Code:
=RIGHT(A1,LEN(A1)-SEARCH("-",A1,1) - 1)
A1 is the string, the rest is the length of the text.
To get the length it searches for the hyphen and subtracts that from the length of A1.
The final -1 gets rid of the space between the hyphen and the text.
 

Read other 2 answers
RELEVANCY SCORE 50.4

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
 

A:What is the formula for Excel

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
 

Read other 2 answers
RELEVANCY SCORE 50.4

Hello All,

I'm using Excel 2003 at work (yes I know it's an old version )

I have the following formula:

=SUMPRODUCT(('ABC''s 2011'!$D$2:$D$194>=DATEVALUE("1/7/2011"))*('ABC''s 2011'!$D$2:$D$194<=DATEVALUE("31/7/2011"))*('ABC''s 2011'!$C$2:$C$194="Ilkeston"))

Basically it looks at a sheet in a workbook and pulls out the number of times 'Ilkeston' appears during 1/7/2011-31/7/2011. Which is fine however I now also want it to exclude the data which also includes 'Not signed'
I was thinking of adding the following -COUNT('ABC''s 2011'!$E$2:$E$194<>"Not signed") But I realise that this will exclude all of the data that includes 'Not signed' and won't just restrict it to the month of July.

Is there a better way of expressing this in a formula? If you have any questions or need anything clarifying please do get in touch.

Thank you in advance
 

A:Help with Excel Formula

I'd need to see the file. =(
 

Read other 1 answers
RELEVANCY SCORE 50.4

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.
 

A:Excel Formula Help

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.
 

Read other 1 answers
RELEVANCY SCORE 50.4

i've read all the help files and even some websupport pages and can't figure this out!! i need to take the numbers i enter into certain cells and add 40% + 100 eg. cell C1 is $520 then I need it to do this 520+40%+100 each and every cell has a different number in it to start with. i'm an idiot so please dont get to tech on me
 

A:a formula in excel

I'll try to explain this as best I can

(using your own example)
C1 = 520
and you want say D1 to provide the total of C1+40% + 100
in cell D1 you would type:
=C1+(C1*40%)+100
just like that. and the total now in D1 should be 828.

then let's say the rest of your numbers are in the C column from C2 through C24 for example.
highlight D1 through D24 and in the Edit menu, select Fill > Fill down and voila you have all the numbers done!

I am assuming a few things here. so if you're doing it in row (across) then you'll have to Fill left or right (depending on where you typed in your original formula.
either way you should only have to type the formula once. I tried it out myself and it worked just fine.

if this doesn't help you out just post back here, and I'm sure someone else can provide you with more help. they're pretty great here
 

Read other 2 answers
RELEVANCY SCORE 50.4

Can someone tell me how to copy a formula into cells below without changing the formula to refer to the cells it is being copied to. For example. If I have a fromula in cell B2 that says B2*D2 and I want it to stay that way how can I prevent it from changing when it is copied into cells below. I want it to say B2*D2 all the way down. Thank you
 

A:Excel Formula

=$B$2*$D$2
For instance, if you want to multiply all of the cells by a number in cell H1, you do this:

=A1*$H$1

Put the dollar sign in front of only the portion(s) you want to have *stuck*.
 

Read other 3 answers
RELEVANCY SCORE 50.4

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.
 

Read other answers
RELEVANCY SCORE 50.4

Attention Excel Gurus,

I had a formula once that searched through a range of text and if it found a duplicate it marked the cell 0 if it was the first or only occurence of the value it returned a 1. it looked something like =IF(a1, A1:A2 = a1,1,0) But that is not it. Does anyone know what I'm talking about?

Thanks

JHorsley
 

A:Looking for Excel Formula

Hi JHorsley,

Do you mean looking for duplicates in a column of text?

Say you have a list of Names down column A,
the following in say cell B2 copied down column B will display "Dup" or "Not Dup" alonside each name in Column A depending on if thet Name exists above it or not.

Replace "Dup" or "Not Dup" to suit .
=IF(COUNTIF(A$1:A2,A2)>1,"Dup","Not Dup")

lol
Hew

Note: no formula in B1 as there is nothing above A1.
Note as you copy the formula down col B, the range will change to include upto that row but the $1 will ensure it always starts from the top.
 

Read other 1 answers
RELEVANCY SCORE 50.4

I currently have a formula that takes the current day, and compares to an Order Picked date.

The formula calculates is the difference between the dates is more than 2.5, "Overdue" is returned in the cell

The problem I encounter is on Mondays and Tuesdays, where I need to change the formula manually to 4.5 and 3.5
because the Sat and Sunday are not skipped and no orders are sent as those are off days. So an order picked on Friday
would show as Overdue on Monday as it is more than 2.5 days.

Original Formula : =IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))

The new Formula: =IF(AND($D$3=”Monday”,A2+4.5<$C$3),”Overdue”, IF(AND($D$3=”Tuesday”,A2+3.5<$C$3),”Overdue”, IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))))

has not worked. Perhaps wrongly positioned parenthesis? It returns #NAME#

A sample spreadsheet is attached.
 

A:Excel IF AND formula and more

Read other 16 answers
RELEVANCY SCORE 50.4

History:
I have two excel sheets, 1) breaks 2) jobs for the day.

In the breaks sheet i have time listed down a5:a50 each cell represents 5 mins. within this sheet on b20:b:24 i have a 20min merged cells break.

On the second excel sheet i have the same time list from the
=now() in a1, showing the time down the page. Again i have sectioned each job in to time periods of merge cells. ie 10min = 2 merged cells, cleaning.

What i want to be able to do is create a formula that will look at the time from the sheet two, (jobs) copy that days jobs to the first sheet, Breaks, find the time and fill down, missing the breaks out and copy below the breaks for the period in time.

I know its a lot.

But if you can Help!!!!
 

A:Help with a excel formula

Would defining rangenames help point to the areas on your second sheet (jobs), then developing a macro to do the math, copy, paste, etc?
 

Read other 2 answers
RELEVANCY SCORE 50.4

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.
 

A:help with excel formula PLEASE

Read other 14 answers
RELEVANCY SCORE 50.4

Question:
If I am trying to add 3 cells and not all 3 cells always contain values. I have formulas in them and if the cell is empty then the #N/A comes up in the cell. So when I have the @sum in another cell to add them it doesn't compute.

Example:
A1 - 5
B1 - #N/A
C1 - 5

I want a formula to add these 3 cells up even when one of them is N/A ??????
 

A:Excel Formula

You should use the IF statement to set the cells to do the function, or set it as a blank value if no numbers are present.

Post the formula you have in the cells that make them show NA.
 

Read other 3 answers
RELEVANCY SCORE 50.4

I need some help with an If statement that has multiple conditions and uses VLOOKUP. Here is the current statement that works, but is only for 1 condition:

=IF(D6="Grand Total","",VLOOKUP(D6,Data!$1:$350,2,FALSE))

I need it to not only test D6 for "Grand Total", but also for "". So, it needs to go something like the below, which doesn't work:

=IF(D6="Grand Total" OR "","",VLOOKUP(D6,Data!$1:$350,2,FALSE))

Thanks in advance for taking the time to help.
 

A:Help with Excel Formula

=IF(OR(D6="Grand Total",D6=""),"",VLOOKUP(D6,Data!$1:$350,2,FALSE))

Try this
 

Read other 2 answers
RELEVANCY SCORE 50.4

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
 

A:help with excel formula

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.
 

Read other 1 answers
RELEVANCY SCORE 50.4

How can I get this formula to leave blank if there is no value in H9?

=IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,"")))

When there is no value in E9, F9 and G9, it still pulls the value of E$8

Thanks
 

A:Excel IF formula help

Read other 14 answers
RELEVANCY SCORE 50.4

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
 

A:Excel Formula help

Read other 11 answers
RELEVANCY SCORE 50.4

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?

Read other answers
RELEVANCY SCORE 50.4

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.
 

A:Excel Formula help

Read other 16 answers
RELEVANCY SCORE 50.4

Excel 2000

I'm looking for some help to write an excel formula:-

If F6 greater than F12 then F6-F12, if F12 greater than F6 then F12-F6.

Can anyone direct me to the greater than/less than symbols?

Thanks
 

A:excel formula

=IF(F6>F12, F6-F12,F12-F6)

or if you just want to ensure that you are getting a positive result

=ABS(F6-F12)
 

Read other 1 answers
RELEVANCY SCORE 50.4

Hi:

I have created a spread sheet to compare 2004 figures with 2005 figures -- with the results showing increase/decrease percentage.

The variance (column N & O) is set up to give me percentage amounts.
Sometimes the percentages are under 999% & some are well over 1000% --
which is fine -- that formula is working fine, but I want it to do more.

Here is what I want ........ if the % amount is over 1000% -- I just want
the cell to read "+1000%"... if it's under 1000%, I want it to read the
true value, whatever that might be between 1% up to 999%, because
sometimes the values are so large -- they just look stupid (ie 1670000%)--
just want to simplify it a little.

Here is the formula I tried: of course it doesn't work!! If it is over 1000% it reads +1000%, but if it is under - it reads FALSE instead of the true value.

=IF(L27/J27>=1000,"+ 1000%"),(IF((L27/J27<999,sum((L27/J27))

Can ya help????
 

A:Excel Formula for IF

Read other 7 answers
RELEVANCY SCORE 50.4

Please see the Attachment, and open as an excel file.
Two formula is selecting the lowest price and then selecting the vendor of the lowest price.

So far if there is no price for one of the vendors for eg, the formula does not work, and if I put a zero in, it selects the zero as best price and vendor.

How can I tell the formula to select the best price but if it shows blank or zero to goto the next lowest price?

Hope I have explained it ok
 

A:Excel formula help

Read other 6 answers
RELEVANCY SCORE 50.4

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
 

A:Excel Formula

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)
 

Read other 1 answers
RELEVANCY SCORE 50.4

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?
 

A:Need Excel Sum Formula

Read other 10 answers
RELEVANCY SCORE 50.4

Hi

Please help me to create an excel formula in one cell that can do the following.
Information is as follow:
<500 units @ 0.56
>500 units @ 0.57
>1000 units @ 0.58

Any help would be much appreciated.
Thanks
 

A:Need help excel formula

Read other 15 answers
RELEVANCY SCORE 50.4

I'm trying to two formulas into one cell so for example I want,
=(C5/60) and =(C5/20) into one cell at lets just say D1
And by using data validation if someone chooses Male in C5 I want the formula in D1 to say =(C5/60).
And if some chooses Female in C5 I want the formula in D1 to say =(C5/20).

Is there any way to do this thanks.
 

A:Excel 07 Formula Help

Read other 8 answers
RELEVANCY SCORE 50.4

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!
 

A:Formula for excel

Read other 16 answers
RELEVANCY SCORE 50.4

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

A:Excel formula

Read other 6 answers
RELEVANCY SCORE 50.4

I have a formula, and one of the elements has changed.

=IF(ISERROR(SEARCH("Barclays",E22,1)), "n/a", E4+1 )

The cell B22 used to have a possible value of "Barclays" (among others). I've now changed the possible values to "Barclays (EUR)" or "Barclays (GBP)" (among others)

Is it possible to say CONTAINS "Barclays" rather than EQUALS "Barclays"?

T

A:Help With Excel Formula

that should be contains

Read other 6 answers
RELEVANCY SCORE 50.4

I need help setting up a new formula to auto populate a field with a specific drop down list based upon an initial selection from a drop down list.....if that makes any sense.

E.G

Column A has a drop down list of 10 items (lets call them 1-10 for this), I have it set so that when any of these options is selected a 'Cost Price' (column B) box auto populates from a master template sheet, and this formula working fine now.

I now want to add a colour section 'column C' which is also a drop down list based on the selection made in column A but depending on the item depends on the colour available.

E.g Column A, drop down options 1-5 have 5 different colour options from a drop down list, Options 6+7 have 3 colour options, 8+9 have one colour option and option 10 has 2 colour options.

I have saved lists for individual items and presume it will be the VLOOKUP command but it's about there I grind to a halt.
Think thats about as well as I can explain at the moment, if required I can post an example document to display exactly how it looks.

Cheers guys
 

A:Further Excel Formula Help

Read other 15 answers
RELEVANCY SCORE 50.4

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.
 

A:Excel formula

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)

 

Read other 1 answers
RELEVANCY SCORE 50.4

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.
 

A:excel formula....help please

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.
 

Read other 1 answers
RELEVANCY SCORE 50.4

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
 

A:Excel Formula Help

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 )
 

Read other 1 answers