Computer Support Forum

Simple Excel Formula Plz

Question: Simple Excel Formula Plz

hi, I just need a simple formula I have a template for an invoice here...

There is the SUBTOTAL
[email protected]%
and there is the TOTAL
The TOTAL is got by adding on a VAT @ 21%....but what formula do I put in so it will calculate the vat every time in the template?

your help would be much appreciated.

Relevance 100%
Preferred Solution: Simple Excel Formula Plz

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

Answer: Simple Excel Formula Plz

The formula would be the subtotal cell (in this example we'll use C5) times .21:

=C5*.21

Then, you would likely format the cell and select "Currency".

2 more replies
Relevance 77.49%

Hi and sorry but I have a very limited knowledge of Excel and need help quickly.I wish to count any cell in a row that has a value in it. The value is not relevant, just the fact that the cell has content.How?

Answer:Simple Excel Formula

The Function is Count. eg. =count(a1:z33) and press enter. Place the cursor in the cell you want the answer to be and type in the Function.

3 more replies
Relevance 76.67%

Help, my sparse hair is becoming even more endangered.We are using Excel XP. Using Autosum I get a total for A1:D1 in I1 and a total for E1:H1 in J1. Simples but, after entering values in A1,B1,C1 and D1 and getting the total in I1, a value entered in E1, F1, G1 or H1 is not only totalled in J1, but is also added onto the original total in I1. If values are entered into E1:H1 before entering values in A1:D1 everything works fine. However we do not always get the second group of values for entering before the first group are available.What is even more puzzling is that for the first 100 or so rows it all worked properly but now, whether we copy the formulae down or enter a new formula for each row we get the same problem.

Answer:Simple (?) Excel formula troubles

Don't use Autosum, it will take in all values from A1 to I1. Use =SUM(A1:D1) in I1 and =SUM(E1:H1) in J1. If I've read you correctly that is.

7 more replies
Relevance 76.67%

I'm trying to write a formula for a spreadsheet in Excel 2010, but I'm obviously not doing it correctly because Excel just recognizes the formula as text. What I want is a formula in cell E6 that produces a value of 30 times the value of Cell C6, but only if the value of Cell D6 ? 0.
What I entered as a formula in Cell E6 is: IF(D6?C6),E6=30*C6
I suspect it needs more or fewer parens or commas or something. Can anyone straighten me out? Many thanks.
 

More replies
Relevance 75.85%

Hi Guys,

Apologies if this is unforgivably dumb but I don't use spreadsheets much and I just can't make it work.

I'm trying to make a home finances spreadsheet, where my first column is everything that comes in, and then I have five or six categorised columns for everything that goes out, ie bills, shopping, clothes etc.

What I would like is a nice neat formula that gives me a running total. So that would be yesterday's total+anything in today-anything out today. But the problem is that I have several "out" columns and I can't make it work when only one of them has a value. I have been using:

=SUM(L3;C4)-(D4:K4)

But I'm getting a !#VALUE! error with this, and even if I type the numbers of each individual negative cell, it will only subtract the first one. I know I can do this without typing each individual cell and without putting negative values in the cells.

Please help!
 

Answer:Excel Financial Formula - Can't get it to do what I want and it should be sooo simple

Would it be possible to see a sample sheet? Obviously you don't want to share all your financial data with the world, but if you could make two or three lines of sample data that can help to explain what you're trying to accomplish.

Without that if I've understood it correctly
If L3 is the previous day's total
C4 is today's income
And D4:K4 encompasses today's expenditure

you could simply use the following in L4
=L3+C4-Sum(D4:K4)
 

2 more replies
Relevance 75.85%

OK, I'm annoyed with myself, cuz I've done this a few years ago for my old company, and want to recreate it for my new one, but can't get my head around it anymore. Should be quite simple for anyone with a bit of working experience with Excel.

In it's most simple form, the table lists stock movements of sample products.
The two main columns in question are:
Serial number: simple number
Movement date: date format
(New location: Text (client's name) only important in the context of information needed, but not vital to the formula)

I want to add a column which will indicate which of the movments of EACH sample was the last one. Then I can filter out those movements which are not the last one, and know where all my individual sample products are.

Logically, I understand that it has to look up all rows with the same serial number, within that group find the record with the greatest value in movement date, and output a certain value indicating it's 'true', but I can't find a formula or combination that'll do it.

Any help would be greatly appreciated
 

Answer:Solved: Simple Excel formula doing my head in...

16 more replies
Relevance 75.85%

OK, I know this must be one of the simplest formulas to ask, but what formula do I use in cell J49, to find 8% of the total of H49. My mind has gone blank.

Doh! its =SUM(H49/10)
 

Answer:Solved: Simple Excel formula needed

not sure that works

8%
=8/100

to find 8% of a value then its

value * (8/100)
 

1 more replies
Relevance 75.85%

Have been using a simple formula (for checking my home bills /account): =E1184+F1185-G1185but at row 1186 instead of a figure it comes up with #NUMBER!Why has this happened?

Answer:Excel simple formula shows #VALUE1

If it is #VALUE! do any of those cells contain text?Try re-entering the values.

3 more replies
Relevance 75.03%

I'm trying to use column A as a 'sequence' number (1, 2, 3, 4, 5, ...) in a spreadsheet, so I enter '1' in cell A1 and enter the simple formula =A1+1 in cell A2 (which correctly returns the number 2). But, when I copy the simple formula in cell A2 and paste it into cell A3, the value in the cell is 2 even tho the formula shows as =A2+1. This works just fine in Excel 97 and 2003, so what am I doing wrong?

Thx for any advice you can give.
 

Answer:Excel 2007 - Pasting a simple formula issue

I've just tested this out on Excel 2007 and it works fine on my PC. I'm at a total loss as to why it would add 1+2 and make 2?
 

6 more replies
Relevance 75.03%

Hello All,

Thank for taking the time.

I have a simple question.

I am organizing a football pool and I need help with a certain formula.

Players are awarded points for their statistics throughout the football season.

I have a simple formula for that. But......

Players are also awarded bonus points for how many yards they amass in a game.

Example:

QuarterBack Passing Yards per game

300-349 Yards Passing = 5 Points
350-399 Yards Passing = 10 Bonus Points
400-449 Yards Passing = 15 Bonus Points
450-499 Yards Passing = 20 Bonus Points
500 + Yards Passing = 30 Bonus Points

I have set up a spreadsheet that tracks all the players stats on a game by game status.

Is there a formula which allows me to set a value for the number of bonus points allowed depending on the value of a cell or cells?
 

Answer:Solved: Need Excel help with a simple formula for Football Pool

9 more replies
Relevance 61.09%

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
 

Answer:Excel 2000 Formula Text Displays instead of Formula Results

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

3 more replies
Relevance 60.27%

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
 

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

14 more replies
Relevance 59.04%

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
 

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

8 more replies
Relevance 59.04%

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.
 

Answer:Excel formula using formula between sheets in workbook

6 more replies
Relevance 56.99%

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.

Answer:Excel Formula to Word Formula

Depends on which version of Word that you are using but Word can do simple calculations

2 more replies
Relevance 56.99%

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.

Answer:Excel Formula to Word Formula

You'll have to do this:

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

2 more replies
Relevance 56.58%

Hi guys,

I'm looking at this question on a test paper for Excel. It says:

"The early booking discount is worked out as follows: if the basic cost is less than £3000, the discount is 3.5% of the basic cost. If it is £3000 or greater, then the discount is 4% of the basic cost. Write this as an IF formula.

Am I right in saying this is the correct way on writing it?

=IF(E6<3000,-3.5%,-4%)

Where E6 is the "basic cost"

thanks,
Matt
 

Answer:Solved: Probably simple IF formula.

Right idea, but incomplete formula
Try something like this
=IF(E6<3000,-.035*e6,-.04*e6)
 

2 more replies
Relevance 56.58%

Hi,Here I am again still trying to learn and somehow lost once more. So i turn to this site which has bailed me out multiple times in hopes that i haven't gone to the well to often.Here's my problem right now. Under column B, I have numbers (1,2,3) and in column C I have letters (a,b,c). I know that by sing the =COUNT formula I can count the number of 1s, 2s, and 3s under column b as well as the number of As, Bs, and C,s under column C. What I am trying to achieve though is to count how many cells that contain "A" are under column C where Column B shows the number 1.I know this is just a formula which I can't seem to find. Hopefully someone can point me to the right direction.Thanks!

Answer:Simple Count formula

To start with, you should be very careful when posting examples of your data.First you said in column C I have letters (a,b,c) Then you said the number of As, Bs, and C,s under column C.When you mix cases, you confuse the poor reader. Are you trying to count lower case letters or uppercase letters?Assuming you meant lower case, a formula of this type should do it for you:=SUMPRODUCT(($B$1:$B$12=1)*($C$1:$C$12="a"))

2 more replies
Relevance 56.58%

We have a data sheet of weights example below in Kg

0.50
0.25
0.01
0.04
0.06
6.00
1.40
1.80

If the weights are the following then value should be as follows

Under 150gms .75
150gms - 1.5Kg 1.75
1.5 Kg - 2 Kg 2.55
2 - 20 Kg 5.25
I then wrote this formulae

=IF(D6<20,5.25,IF(D6<2,2.55,IF(D6<1.5,1.75,IF(D6<0.15,0.75))))

However the answer is coming 5.25 for each cell. I do not understand. Can someone help.

Many Thanks
 

Answer:Need some help in a formula which i thought is quite simple

7 more replies
Relevance 56.17%

I have a Google spreadsheet with 2 separate columns of over 200 lines long each.

I have spend 2 days looking for a formula that will count the empty cells (the full cells have no numeric values in them, just a X) I have tried every combination possible......COUNTBLANK, COUNT, ISBLANK, ISNUMBER......

Can someone be so kind as to tell me the exact wording I need to write in the formula bar.......

I know it should be really simple but it's not to me......

:confused

Thank you

Onnha
 

Answer:Google Spreadsheets....need a simple formula

Perhaps
https://support.google.com/drive/bin/static.py?hl=en&topic=25273&page=table.cs&tab=1240290
ISBLANK
if not remove the word INFO in the filter box and look at other commands.
 

4 more replies
Relevance 56.17%

Hello,I have created the macro below. I recorded it so that when I tap the button the macro is attached to it inserts a new row above my designeted cell (IMP_01). One of the cells in the new row also has a formula in it which makes it equal 2 of the other cells in the row multiplied by each other. The problem is the first time I run the macro everything works fine. The second time I run the macro the formula does not work/appear in the new row. In addition, I am using this macro and other similar ones to add new rows and 3 different sections of the same spreadsheet.Any help would be awesome, thanks!Range("IMP_01").Select Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("E29").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" Range("IMP_01").SelectEnd Sub

Answer:Simple formula is not working in macro

First, a posting tip...Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. As for your question, your code selects E29 and puts the formula in that cell. That's all it's ever going to do since you've hardcoded that range reference. It's going to put the formula in E29 every time you run the macro.In addition, you do not have to Select a range via VBA to perform an action on it. That's very inefficient. You can almost always perform the operation directly on the range within VBA.Try this. It inserts a row above the Named range and then uses the Row property of the Named range to determine where to put the formula. Basically you are letting VBA figure out what row to put the formula in instead of telling it to put the formula in a specific cell.Sub InsertFormula()
'Insert row above named range
Range("IMP_01").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'Put formula in new row
Range("E" & Range("IMP_01").Row - 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 52.07%

Need help with the below excel formula to ensure the maximum value doesnt exceed 1200 - Can anyone help?=IF(AB14>59,Z14+1,ROUND(Z14+AB14/100+0.0005,2))[email protected] by moderator: email address deleted

Answer:Excel Formula Help Excel 2010

Ok, so did my suggested formula wok for you or not?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 52.07%

Hello All,
I am having excel file aaa which is saved on my one drive . File aaa has refrence from file bbb which is saved on local drive of my laptop. Is it possible if i use aaa from another machine and if a make any changes in bbb from my computer will that reflect in aaa.

More replies
Relevance 50.43%
Question: Excel Formula

Using Excel spreadsheet, is there a way of entering a date of birth in one cell for the program to automatically calculate a person's age in another cell, perhaps using the PC date for the clculation?Just years and months, or rounded to a decimal of their age (29.5 etc)DRiM

Answer:Excel Formula

Use DATEDIF click here

6 more replies
Relevance 50.43%
Question: Excel Formula

Hi

Does anyone know a quick method or type of formula I might need to do the following:

I have a spread sheet which i record new figures on everyweek and have different catergories, with the dates monday to friday down the side

Monday to friday dates are repeated about 15 times for the 15 diff categories and the moment I have to manually key them in the 1st category and then copy and paste them into the rest

Surely there is a better way?

Thanks in advance
 

Answer:Excel Formula

I'm not sure I understand your situation. For each category, you have a column with the day (Monday through Friday) and then you have another column with the dates for each day. Is that it?

So, the day cells are always simply Monday through Friday but the corresponding dates change each week. Is that it?

Suppose cells A1 through A5 are Monday through Friday, and cells B1 through B5 are Oct. 20 through Oct. 25. This would be for the first category, right? Let's say you leave a blank row between categories. So, the next category is in cells A7:A11 through B7:B11. And, so on, for all categories.

Cell B1 shows Oct. 20, which you entered. To show the same date in B7, insert the formula =B1. Enter the corresponding formula in cells B8:B11. Now, copy the formulas in B7:B11 down to each of the other categories.

Once all the formulas are setup, save this file as a template that you can use for subsequent weeks. Just change the dates in the first section (cells B1:B5) and the dates in the other categories will be updated by formula.

If I'm off the track here, can you attach an Excel file to a forum message so that I/we can take a look at it?
 

2 more replies
Relevance 50.43%

Ok basically, this is what I've got.
I am trying to make a formula that can find errors. I'm am not an excel guru by any means.

This thing basically needs to have an if then statement something like this:

IF the value is less than .300 from any other value THEN Null (invalid entry message)

oh yeah... The data that needs to be compared is going to be in column E lines 2-51
 

Answer:Need formula for Excel. Please help!

can you upload an example spreadsheet, would help to see the values you are comparing

are you comparing E2 through E51 and want to compare the values against each for a variance less then .3
 

3 more replies
Relevance 50.43%
Question: Excel Formula Help

Please can anyone help with excel formulas say I have this formula=COUNTIF(A1:F1,"M")*8 but I need the formula to only apply to one or two columns in the same row say A1 and C1 need to return the number *8 but columns B1 and D1 need to return the number *4 and then the results added together as one answer 8+4+8+4=24 Can the be written in one formula? Steve.

Answer:Excel Formula Help

=(A1="M")*8+(B1="M")*4+(C1="M")*8+(D1="M")*4

3 more replies
Relevance 50.43%
Question: Formula in excel

Hi, I am sakona, I want to know about some formula bellow,I have score like these: 10,12,20,8,A,F,19 I want to add 3point on that, after add, if it bigger than 20 please equal 20, if less than or equal 20 keep it the same score but if score equal "A" or "F" please keep it.Thaks,

Answer:Formula in excel

I'm going to move this to the Office forum, but I doubt they want to do your homework, either.

3 more replies
Relevance 50.43%
Question: Excel Formula Help

I have a Excel sheet that shows budget figures, I done conditional formatting so that if it is under budget green and over budget is red, but I want to put a formula at the bottom that will count the total number of cells within the range which is named supplyfigure, then tell me out of all those how many times I was within budget.

Example of what I am looking for

Within Budget 25/48

the first number being the number of times I was within budget and the second is the total amount of cells within the range.

Thanks
Jay Morlan
 

Answer:Excel Formula Help

Would the COUNT function work to give you the total number of cells within a range of cells that had numeric values. Would the COUNTIF function work to give you the number of cells within a range of cells that met a particular criteria like being greater than or equal to zero?

If those functions will work to get the two numbers, you could then use the "&" operator (See the help information for CONCATENATE) to build up your desired text string.
 

2 more replies
Relevance 50.43%

Hi all,

I remember several years ago that I used an MS Excel formula to access a single cell in a table using named ranges, but alas, I cannot remember what it is.
I think it is something like =(EF,RS) where EF is a named row and RS is a named column. Therefore the formula should access the cell containing 89. as in the table below:

.... PQ RS TU VW
AB 15 68 95 12
CD 45 78 37 28
EF 59 89 18 32
GH 38 46 49 52


The formula =sum(EF,RS) works but produces the result of 59+89+18+32(EF) plus 68+78+89+46(RS) totalling 479.
But what is the formula access only a single cell? Can anyone remind what the formula is?
 

Answer:MS Excel formula help

Did you already look at this?

http://support.microsoft.com/kb/324861

They have some different commands there like lookup and index and a link to further information on finding data in different ways.
 

8 more replies
Relevance 50.43%

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.
 

Answer:Excel 07 Formula Help

8 more replies
Relevance 50.43%
Question: Excel Formula Help

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?
 

Answer:Excel Formula 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"))
 

2 more replies
Relevance 50.43%

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
 

Answer:Further Excel Formula Help

15 more replies
Relevance 50.43%
Question: ytd formula excel

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
 

Answer: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
 

1 more replies
Relevance 50.43%

pls help,can somebody write me a simple formula (for me is complicated) in excel.what I need is this:I have D14 sheet in which I enter lenght.in H19 sheet I need formula which will do this:if lenght is up 25 then enter 200 in H19sheetif lenght is beetwen 25 and 50,or equal 25 than enter 300and if lenght is bigger than 50 or equal 50 than enter 400 in that H19 sheet.tnx :)

Answer:if formula in excel

Try this:=IF(AND(D14>0,D14<=25),200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,"")))MIKEhttp://www.skeptic.com/

40 more replies
Relevance 50.43%

Hey,Im building a spreadsheet of my ebay transactions. If you recieve payment from outside the UK the paypal charge is different from that inside the UK (boarder fees click here=)I have a coulmn called "Payment from inside UK?" In this column i put "yes" or "no". The next column is the paypal fees. I want to do an if formula, i think, to see if that column says "yes" then do this formula for charges, if "no" then this. Here is my failed attempt.=IF((K37=Yes,"=SUM(J37*3.4%+0.2)","=SUM(J37*3.9%+0.2)")(Column K is my Payment from inside UK?Column J is the price sold)Any ideas?Thanks for the help.

Answer:Help with an excel formula please...

=IF(K37="Yes", J37*3.4%+0.2, J37*3.9%+0.2)

6 more replies
Relevance 50.43%
Question: Excel Formula Help

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.
 

Answer:Excel Formula Help

13 more replies
Relevance 50.43%
Question: excel formula

I am trying to find a formula to rank players in a league.At present I use Rank formula but it doesn't show a true ranking position.The ranking should be on frame diff & then on wins but at present it is only on frame diff.I can send a example to anyone with any ideas.

More replies
Relevance 50.43%

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
 

Answer:Help with Excel Formula

I'd need to see the file. =(
 

1 more replies
Relevance 50.43%

Hi, I asked a question about an Excel formula a couple of weeks back, And got several replies all very good, Except I struggle with excel So I decided in my wisdom, to go and try and find a ready made program that would do what I needed, Well basically I failed, So Here I am back cap in hand asking if anyone can help.What I require is a Excel formula that in column A Date of purchase,column BItem Purchased,Column C Purchase Price,Column D Sale Date, Column E Sale Price, Column F profit or loss on that Item, and finally Column G Running profit / Loss on all Items.If anyone Has a spare moment could they make a formula and send it to my via email I would be so grateful.This as been a project that i have been trying to sort for so long.But I just don't Understand Excel.Hoping someone can help Tony

Answer:excel formula again

Did this not help? click here

6 more replies
Relevance 50.43%
Question: Excel Formula Help

Greetings to all,I feel a bit rude joining up and requesting help immeadiately without having anything to offer but I hope to be able to offer up input at a later date.....My problem has caused untold amounts of hair pulling and cussing at my computer screen and as you can tell by my being here pleading for help , abject failure!I will outline my requirements and have my fingers crossed somebody can offer a solution.I have a multi columned excel spreadsheet that I envisage growing substantially in the next 6 months. The Data/Sort function and Subtotals are sufficient at present but as volume increases I need to lower actual analysis time.I am trying to set up a "cockpit" that will read from the spreadsheet page when I specify certain parameters to it.Heres the eg....The list covers columns A-N and carries alpha and numerical info. I list a "job number" in column "C".I want to be able to list a specfic job number in the cockpit and excel to list all relevant information thru columns A-N onto the cockpit page beneath the input code. Take into account some jobs are multi lined hence the formula needs to understand it is reading the whole page/list to discover the necessary info.My best attempt was =IF(Sheet2!C2:C280=Cockpit!B20,+Sheet2!A2:N2,0) in the vain attempt to at least list one line.....i guess i've missed something....I throw myself at the mercy of strangers and hope somebody canA/ Decipher my above gibberingsB/ Provide a solution.Kind Regards to you all.Gary B.

Answer:Excel Formula Help

To get a single line you should be able to use VLOOKUP - there is a tutorial click hereIf you want to return information from multiple rows, I think that you may need a VBA (macro) solution, unless somebody knows differently.

5 more replies
Relevance 50.43%

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.
 

Answer:Excel IF AND formula and more

16 more replies
Relevance 50.43%
Question: Excel Formula

Currently increasing a field (AJ2) based on certain criteria (If the designated cells (C5:C20 has and "X" in any of the column cells) (AJ2) would increase by (1) Now need to expand that criteria to include some additional entries as indicated at the bottom but exclude an entry of "N/C".

Current senerio;
AJ2 =SUMPRODUCT((MOD(COLUMN(C38:AZ38),2)=1)*C38:AZ38)

C38 =SUMPRODUCT((C3<>"")*(COUNTIF(C5:C20,"X")>0))

Need to expand the "X" in range C5:C20 to not be limited to just "X" but be expanded to include one of 10 specific text inputs like AWL, E/P, E/V or (7 other selected text inputs)

Thank you in advance
Winger
 

Answer:Excel Formula

Winger, I cannot for the life of me work out ½ of what you're trying to do here. For instance, COLUMN(C38:AZ38) just gives you the column # for C:C, ie: 3.

This is a complete stab in the dark, but how about if you amended to something like

COUNTIF(C5:C20,{"X","Y","Z"})

?

HTH,
Andy
 

2 more replies
Relevance 50.43%
Question: Excel formula bar

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
 

More replies
Relevance 50.43%
Question: Excel Formula

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.
 

Answer:Excel Formula

10 more replies
Relevance 50.43%

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
 

Answer:help with excel formula

6 more replies
Relevance 50.43%
Question: Excel Formula Help

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!
 

More replies
Relevance 50.43%
Question: Excel formula

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?
 

Answer:Excel formula

6 more replies
Relevance 50.43%

Am struggling with an XL sheet and cannot decipher the manual at all to help me. I have a range of cells, split in rows, some of which are blank and contain no data. I wish to either seperate them from the data containing cells, or just delete them. Can anyone point me in the right direction please ?

Answer:Help with EXCEL formula

To delete a blank row, click in it, Edit > Delete, tick Entire Row.To select several rows, click in the first one then hold the CTRL key down and click in the others.

6 more replies
Relevance 50.43%
Question: Excel formula

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

Answer:Excel formula

6 more replies
Relevance 50.43%

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?

More replies
Relevance 50.43%
Question: Excel formula

Hi, Can you help?I want to hide any negative numbers(sums) in my spreadsheet, but when they become positive display the results. I think i need the if formula but i cant suss it out. Iknow one of you brainy lot can help ;-}

Answer:Excel formula

I'm no expert but here's a quick tip -In a recent version of Excel, do format cells, number, custom and from the drop down, select the format that has negative numbers in red. Simply change the "red" to "white" and the -ve numbers are hidden (sort of).You could use IF to refer to the numbers (which could be in a hidden column) and have, for example, =IF(G11<0,"",G11)in a new column to display the p+ve numbers only - which would do the trick.Hope that makes sense. No doubt some genius out there will have a better method though!

4 more replies
Relevance 50.43%

I have been asked to create an expense spreadsheet where Column A and B are date and text. Column C needs to be where you insert 1 through to 23 (representing an expense Code reference) with the value going into Column D. How do I create the formula that if the user inserts which ever expense code it will appear in column F and continually add the figures automatically. So for example if the user type in Line 1 code 1 and 20.00 for meals it auto goes in Column F then he also adds in Line 10 per say, another meal, that cost is auto added to the existing figure in Column F being the cumulative figure for the month.I hope that this can be done. Any help would be much appreciated. I have basic knowledge of Excel not advanced.

Answer:Excel Formula Help Please

I hope I've understood this correctly, Lozzy. If you just want Column F to contain a running total of expenses, irrespectice of the code in column C then:Assuming that you have titles in row 1 and the data starts in row 2 then in F2 enter the formula:=SUM(D$2:D2)and press Enter. Then hover the cursor over the bottom right of cell F2 until it turns into a +. Hold down the left mouse button and drag down as far as required. This will copy the formula down, for example in F4 it should read=SUM(D$2:D4)and give a running total.

4 more replies
Relevance 50.43%

FOB Value <500 500-1000 1000-2000 >2000
A 5 10 15 20
B 6 11 16 21
C 7 12 17 22
D 8 13 18 23



FOB 775


CHARGES A 10

Looking at the above chart based on each customer A,B,C & D PRICES HAVE BEEN DEFINED ACCORDING TO THE FOB VALUE RANGE. NOW I WANT TO MAKE A FORMULA WHAT WOULD BE THE CHARGES ON CHANGING THE FOB VALUE AGAINST EACH CUSTOMER.
 

More replies
Relevance 50.43%

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
 

Answer:Help with excel formula

7 more replies
Relevance 50.43%
Question: EXCEL Formula help

Hello I'm creating a spreadsheet and need some help on what formula to use- I only want excel to work out the vat if payment method is either Online or cheque, I believe its a sumif statement something like "=SUMif(D3=Online,Cheque) =SUM(G3*20/100)" but i keep getting error message as I don't have much experience in excel and can only remember the basics from ICT at high school

heres a link to the spreadsheet from my cloud storage https://copy.com/XZvqcSqUpOUM7eUe
any help would be much appreciated many thanks

Answer:EXCEL Formula help

Code:
=SUMIF(D3:D5, "Cheque",I3:I5)+SUMIF(D3:D5, "Online", I3:I5)
would work

EDIT: oh oh oh wait, i see what you mean. Hold on
=IF(CELL="Online",CELL+VAT,IF(CELL="Cheque",CELL+V AT, CELL))

So in other words...
if cell == online, calculate it with VAT. If it isn't == online, check if it's equal to cheque. If it is, calculate it with VAT, if it isn't, just display the contents of the cell, or in your case, 0

So more specifically tailored to your sheet here.... Column H would contain

Code:
=IF(D3="Online",SUM(G3*20/100),IF(D3="Cheque",SUM(G3*20/100),0))

1 more replies
Relevance 50.43%
Question: Excel Formula

Hi

Would be grateful for advice on what formula to use for the below.

I am trying to find out the average number of days for each job type completed (i.e. the average number of days for job types emergency, urgent & routine that have been completed). How do I write the conditional statement to do this (assuming that this can be done).
What formula do I use to get a combined average number of days to complete jobs for all completed job types (Emergency, urgent & routine).
Each job type has a target for completion, Emergency has a target date of 3 days for completion, urgent has a target date of 7 days for completion and routine has a target date of 28 days for completion. I would like to count the number of job types that were completed within target. In the example below, two of the routines were completed within target of 28 days, the data for this is the number of days to complete job is caluclated from the date received/date completed. Again, how do I do this.
I have attached the file that relates to the above.
Many thanks in advance
 

Answer:Excel Formula

Hi Kets23,

I've updated your workbook with two techniques.

1 & 2. To get the averages, easiest is to use Pivot Table - needs to be refreshed whenever you change data in the source.
This shows you the averages per Job Type and the overall average.

3. I've used an array formula (needs to be entered with Ctrl + Shift + Enter) to count how many jobs hit target.

I also named the ranges so make the formulas more understandable.
Removed two blank columns to aid the Pivot Table.

Suggest you use HELP to get info on the above techniques.

lol
Hew
 

2 more replies
Relevance 50.43%

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

Answer:Help with an Excel formula

6 more replies
Relevance 50.43%

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

Answer:Formula Bar MS Excel (XP)

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
 

1 more replies
Relevance 50.43%
Question: excel formula help

I have a spreadsheet that has 3 columns, the third column is called category and some of the rows have multiple categories separated by a semi-colon. I would like to find the ; and then copy an additional row for each additional item in that cell and make them a new row with individual categories
for example:
name phone category
ABC 555-5555 building; foundations; building; electrical
so what I would like to end up with is:
ABC 555-5555 building
ABC 555-5555 foundations
ABC 555-5555 Roofing
ABC 555-5555 electrical
I am attaching a spreadsheet.
Please help!
 

Answer:excel formula help

Hi
The only way (I can think of) to do this is with a macro. The attached has the macro called "Split_Activities" that will create a new worksheet, deposit the results there and sort on Company.
See if this is what you want and let me know if there are any changes you need
 

3 more replies
Relevance 50.43%

I am creating a spreadsheet logging the times staff are present each day and i am trying to calculate a total number of hours worked formula and if on that day no hours are worked, it is showing as ##### instead of 0.I know this might be not enough information for someone to assist me directly but does anyone know of where i can look to get help.Many thanks

Answer:Help with Excel formula

Are you subtracting times to get that result? Excel by default will not display negative times and puts in ##### There are two ways around this:a) Use a formula like =MOD(A1-B1,1) which will display a positive time even if B1 is greater than A1.b) In Tools > Options, switch to the 1904 date system.

10 more replies
Relevance 50.43%
Question: excel formula

i have a list of agents with several log ins and log outs for one day, i was trying to do a vlookup to only get the name of the agent and the first log in and the last log out i am not too familiar with this and i got stock can some body please help me with a formula for this, atached you will see the excel sheet with the info that i got from the agents log in and out.

Thanks in advance.
<Removed all the names and replaced with EMP - Number - ETAF moderator

Note: we only need dummy data - and as this is a public forum not a good idea to have real names , i assume they where , in the public domain

also solution in my last post #4 >
 

Answer:excel formula

6 more replies
Relevance 50.43%
Question: EXCEL Formula Help

I'm new here so I hope this site will be helpful!

I have a sample Excel spreadsheet report attached.

In it, I have 2 tables. What I need is to copy data from Table 2 and fill in the missing blanks in Table 1. The Company Name and Email must be grouped to the name so the 'Name' in table one must have the same email and company name as in the 'Name' in Table 2.

Some of the 'Names' are the same in the tables, some names aren't there, and some names are extra. The names that aren't there must have their email and company name empty, but still need to be there at the final product. Would really appreciate some help.

I then proceeded to use the SUMIF function but that only works for numbers,

i.e

I had grouped the emails and company names to the Names in column two.

I tried to use the SUMIF function, in the case of the attached file, =SUMIF(H:H,A:A,G:G) but it did not work.

I then tried converting the words into numbers but was unsuccessful,

I would appreciate any help.

Thank you!
 

Answer:EXCEL Formula Help

10 more replies
Relevance 50.43%

I'm rustier than I thought with Excel, so here goes:

I'm creating a sales report spreadsheet. Cell C2 will be the invoiced amount. Cell Q2 is the sales rep's ID. Cell R2 is the commission (C2*.2) for the rep. Cell U2 is the company net (invoice minus commission). If there is a sales rep assigned to the client, how can a value in Q2 be worked into a formula so that if there is a rep their commission will show in R2 and the company's net in U2, but if there is no rep, there will be "zero" in R2 and the full invoice amount in U2? Must there be a number value in Q2, or will any data in Q2 work?

It should be an "IF-THEN" situation, but I'm obviously missing on the fine points of creating this formula.

I hope that I've made myself clear. Thanks.
 

Answer:Need an Excel Formula

6 more replies
Relevance 50.43%
Question: Excel formula help

I am looking to do a time card thing on excel where workers fill out their time on their smartphones. I have attached an example of what it will look like for clarification. I need the employee to be able to put a pin number in so we can verify that each employee filled out their own hours. My question is what kind of formula can I use to check that the pin goes with the employees name and then it gives me a "true, false" or something that tells me if the pin is correct?

Hopefully this made sense.

Thanks.
 

Answer:Excel formula help

12 more replies
Relevance 50.43%
Question: Excel Formula

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
 

Answer:Excel Formula

12 more replies
Relevance 50.43%

HI - I'm trying to get a % in excel on service levels met.
Example:
Date Expected to be complete - 10/12/2015
Date Completed - 10/13/2015
Can someone help me figure out a formula?

Answer:need formula for % in Excel

I doubt it as there aren't 13 months in 2015.

4 more replies
Relevance 50.43%
Question: Excel vat formula

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.
 

Answer:Excel vat formula

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?
 

2 more replies
Relevance 50.43%
Question: excel formula help

I haven't used Excel before except for basic functionality, but in the degree course I am studying we are using Excel to calculate probability. I have been given all the functions in the assignment and a photocopy of how the spreadsheet should look. However the random numbers need to extend over 500 rows and when i copy and paste row1 to row500 it will not generate the 500 results only the first.Can anybody help in what I maybe doing wrong?

Answer:excel formula help

tends to confuse, and the habit should be avoided :-)In the meantime does VoG's Excel Tips and Tricks click here solve your problem?Hope this helps :-)

8 more replies
Relevance 50.43%

Hi,

I am working on a project with pulling total sum of values from one worksheet (named AR) to the other worksheet (named Joblog). I want to get the running total for the specific job number listed in the "Joblog" worksheet to reflect in the column with heading "Invoiced to date". The information will be pulled from the worksheet called "AR". I started to use this formula but it is not giving me the running total.

=SUMPRODUCT(INDEX(AR!F:F,MATCH(A2,AR!B:B,0)))

I've attached the file I have. Any help on this is truly appreciated...
 

Answer:Excel: Help with sum formula

"=SUMIF(AR!d:d,'JOB LOG'!C2,AR!F:F)" Without the quotes should work.
 

2 more replies
Relevance 50.43%
Question: Excel Formula

Hello, Can anyone help me with this little query? I have a list of names in a column on an Excel worksheet. I would like the cells in another column to pickup just the first initial from the original column. E.g.Column A Column BJames J Lee LDeborah DSarah SRichard RJohn J....and so on.Thank you in advance for any help.Lee

Answer:Excel Formula

have a look at this website.Fantastic free tutorials.Hope this helps

4 more replies
Relevance 50.43%

What im trying to do is get excel to look inside a different excel file, and get the latest CPK value. to do this i need excel to check sheet 12 and see if there is a value for the cpk, if not excel should check sheet 11 and so on untill it gets to sheet 1, when it gets to sheet1, if there is no cpk excel needs to see if there is a provisional value and if not, return the answer "NO CPK"

i have put together this formula and it seems to work ok when i like 4 cells in the same sheet but when trying to use it as above it stops on the IF before sheet 4 (hilighted in red)

=IF('[01.xls]Sheet 12'!$AG$16<>"",'[01.xls]Sheet 12'!$AG$16,IF('[01.xls]Sheet 11'!$AG$16<>"",'[01.xls]Sheet 11'!$AG$16,IF('[01.xls]Sheet 10'!$AG$16<>"",'[01.xls]Sheet 10'!$AG$16,IF('[01.xls]Sheet 9'!$AG$16<>"",'[01.xls]Sheet 9'!$AG$16,IF('[01.xls]Sheet 8'!$AG$16<>"",'[01.xls]Sheet 8'!$AG$16,IF('[01.xls]Sheet 7'!$AG$16<>"",'[01.xls]Sheet 7'!$AG$16,IF('[01.xls]Sheet 6'!$AG$16<>"",'[01.xls]Sheet 6'!$AG$16,IF('[01.xls]Sheet 5'!$AG$16<>"",'[01.xls]Sheet 5'!$AG$16,IF('[01.xls]Sheet 4'!$AG$16<>"",'[01.xls]Sheet 4'!$AG$16,IF('[01.xls]Sheet 3'!$AG$16<>"",'[01.xl... Read more

Answer:Help with an excel formula

Excel limits the number of nested ifs to 7.
 

3 more replies
Relevance 50.43%
Question: Excel formula

Hi Guys,
Pls check out the excel sheet attached and let me know if what I want is possible.
Thanks in advance
 

Answer:Excel formula

6 more replies
Relevance 50.43%

Hi,

I am currently using the following Excel formula in cell K2 to calculate and display the average of the lowest two values in the range G2 to J2.

=SUM(SMALL(G2:J2,1),SMALL(G2:J2,2))/2

The formula works fine, but I now want it to ignore blank cells, so that if only one value is present in the range (it will be the value already in G2 from a previous season) it will display that value in K2 in the absence of data in H2,I2 and J2. However, I still want the averaging function of the current formula to happen once cells H2 to J2 start having data added.

I would be grateful for any help or hints as to how to proceed.

 

Answer:Help with Excel Formula

Welcome to the forum Macca.

Does this do what you need?...

=IF(AND(ISBLANK(H2),ISBLANK(I2),ISBLANK(J2)),G2,SUM(SMALL(G2:J2,1),SMALL(G2:J2,2))/2)
 

2 more replies
Relevance 50.43%

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
 

Answer:Help with Excel formula

16 more replies
Relevance 50.43%
Question: Excel Formula Help

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,
 

Answer:Excel Formula Help

11 more replies
Relevance 50.43%
Question: Excel Formula

I'm creating an attendance tracker for my employer. Row A is the date of occurrence (i.e. employee was late/absent), Row B is the value of the attendance issue (1 for being absent, 0.5 for being late/leaving early), Row C is running total, Row D is the date the value is to drop off (6 months after receiving). I'm trying to create a formula so that when today's date is equal to or after the drop date in Row D, the value in row B will automatically drop off in row C. Does anyone know how to do that?

Thanks!
 

Answer:Excel Formula

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.

An IF in column B may work
if C2 date is =or greater than date(Year(today()), Month(today())-6,day(today())
then , start sum again
 

1 more replies
Relevance 50.43%
Question: Excel Formula Help

So I have an issue...Hopefully someone here is literate enough in Excel to help me.
I am using this function to reference items in a table. The problem is the lookup value A9 changes, and I need the table array to change with it.

So this is the function:
Assume Cell A9 says Service, then this will work.

=VLOOKUP($A9,Service_Array,3,FALSE)

However if Cell A9 says Lease I need the function to dynamically change to say this:

=VLOOKUP($A9,Lease_Array,3,FALSE)

Somehow I need to reference A9 to change the table array option in this function.
 

Answer:Excel Formula Help

=VLOOKUP($A9,IF($A9="Lease",INDIRECT("Lease_Array"),INDIRECT("Service_array")),3,FALSE)
 

5 more replies
Relevance 50.43%
Question: Excel Formula help

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
 

Answer:Excel Formula help

12 more replies
Relevance 50.43%
Question: Formula's in Excel

Is there a Formula within excel which allows you to use, the "Sheet Name" as a cell value... i.e if you change the sheet name a particular cell changes to?thanksSteve

Answer:Formula's in Excel

you are renaming the sheet to something from a predetermined list of names, (which is elsewhere on the sheet), it would be possible.If Not then VBA would be needed.click hereandclick hereGood luck!Regards,Simsy

2 more replies
Relevance 50.43%

I must be getting rusty on my Excel formulas as I worked this out a few years back.

I have a spreadsheet to help my work out my computer system pricing.
Right now, I have COST + MARGIN = SELL PRICE

Right now, I need help on my MARGIN formula. When I sell a computer system, I want to be able to make 15% of COST PRICE, but no less than$100

15% of COST PRICE is easy
=1+(COST*15%)

However the hard part is IF 1+(COST*15%) = Less Than $100, I do want the value = $100

I am just a little lost on how i put that formula into Excel.
BTW, I do know that LESS THAN is >


At the end of the day, I want some thing that my co-worker can use without him complain then he has to manually have to work things out.

=if((cost*15%)>100,cost*15%,100)

Answer:Excel Formula Help (IF, THEN, LESS THAN)

or you could just do this

=max((cost*1.15), 100)

Sorry that should be max not min above (I can't edit for some reason, otherwise I would change it)

*Mod note*

I edited it for you bla!! I think there is a time frame on the edits.

Mak

1 more replies
Relevance 50.43%

I'm working on creating an excel formula to calculate a permit for the city. I need to have a formula that calcuates a sum of numbers, B12:B49, and multiplies it by a $1.60, which I have in G142. The problem I am having is that after $200 dollars, the muliplier changes to $1.07, in H142. There is also a minimum charge of $74.76, in I142, that needs to show up if not met. Any and all information will be very helpful. Thank you,Patrick

Answer:Need help with excel formula

re: "The problem I am having is that after $200 dollars, the muliplier changes to $1.07, in H142."When does the $200 limit come into effect, after SUM(G142) exceeds $200 or after SUM(B12:B49)*1.60 exceeds $200?Basically what you need is a nested IF. Let's assume the $200 limit is based on the SUM alone.Try this and let me know how it works:=IF(SUM(B12:B49)>200, SUM(B12:B49)*H142, IF(SUM(B12:B49)*G142<I142, I142, SUM(B12:B49)*G142))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 50.43%

I've recently bought the 2010 excel I can't change the into currency or enter a date for : 22/08/11. I have to type in 22 august 2011. I want to total a column instead I get a formula.

Answer:excel gives a formula instead of a sum

You should ask this in the Office forum.You can change number, date, and currency type for a cell or column by highlighting, right clicking and selecting format cell.You can add up a column by putting a formula in a cell and adding the cell range such as:In B14 you type: =SUM(B2:B13) which will add up all numbers in cells B2 through B13 and show the sum in B14. These can be regular numbers or currency, but I doubt that dates will give you any meaningful results.You have to be a little bit crazy to keep you from going insane.

13 more replies
Relevance 50.43%

I know how to do the basics, sums, counts, averages....

But I have a spreadsheet that has several culumns with each row being a different date. I need to set up formula's in cells off to the side that take a sum or average from numbers in one column, but not all the numbers just the ones in certain rows. For example if I had a column of names, and a column of numbers. I would need to get the sum from the colum of numbers for a certain name only. That name appearing several times, not just once, in the column of names along with many other names in that same column.

It's more complicated then that since I would have do this with several different columns that are names, locations, and various other info that happened on each date but if you could tell me a formula command to do this that would be great.
 

Answer:Help with Excel formula's

=sumif(range, criteria, sumrange)

Also, look at the whole database section of functions. They all begin with "d", e.g. dcount, dsum, dmin, dmax, dget, etc.
 

2 more replies
Relevance 50.43%

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.
 

More replies
Relevance 50.43%
Question: Excel formula help

Unfortunately my strength with Excel is weak, and I'm not entirely sure what to search for in general...

I have a spreadsheet with a series of dates in Row 1 (with the exception of A1), data corresponding to the date in rows 2-12, and then a series of formulas below that. The dates are not completely consecutive; they correspond to "weekdays," and as such exclude weekends and holidays. I'd like to track certain changes over certain time periods, such as 1 day, 1 week, 1 month, etc.

Here's the question: if the date in the column is, say, 08/31/2010, and I want to have Excel select a cell from a column one month prior, is there a way to phrase that in a formula? (i.e., it would select a cell from the column starting with 07/31/2010, or, since that was a Saturday and isn't in the chart, select from the row starting with 07/30/2010 instead). The same question applies to 1 week prior, or 1 year prior, for that matter.

I'm suspecting that this is either significantly easier or significantly more complicated than I think it is; either way, I'd appreciate help, or at least alternative suggestions.
 

Answer:Excel formula help

You can use the DATEDIF function for some of this.

To keep things easy on yourself, try splitting up the formula's logic into separable pieces. Makes troubleshooting easy, and ensures you see the correct information at each step. You can combine and refactor later once you're sure the approach you're trying is successful.
Edit: You'll likely need to write a little VBA to look for a particular cell by its value. Here's one example.
 

2 more replies
Relevance 50.43%
Question: Excel formula

Hi,

Can someone please tell me how this formula works:

{=SUM(($F$74:$F$123)*($M$74:$M$123>=G135)*($W$74:$W$123>=G135))/4*(1+Data!$AI$23)^(G135/4)}

All the data is numerical and the result that I get from the formula is simply ($F$74:$F$123)/4.

Thanks.
 

Answer:Excel formula

First, you should be getting a numerical result, so click in the cell, go to Format, Cell and select General. Other formats may work, but apply them later.
Next, the {} means this is an array formula (some people call them CSE formulas - you will see why soon). You don't type the {} in yourself - you type in the formula and hold down Control, Shift and hit Enter (hence CSE) and Excel adds the brackets automatically.
Next, you have several ranges, such as $F$74:$F$123. Just for clarity, I will point out that the range could be written as F74:F123 but that would be a relative range - if you copied the formula and pasted it elsewhere, Excel would alter the range to match. Having the $ in the formula makes these absolute values, so no matter where the formula is pasted, the range looked at is always this one (or, rather, these ranges.)
Your formula does several things, so let me break them up.
{=SUM(($F$74:$F$123)*($M$74:$M$123>=G135)*($W$74:$W$123>=G135))/4*(1+Data!$AI$23)^(G135/4)}
Any portion like
=SUM($F$74:$F$75)>=G135 (also an array formula)
looks at the sum in the range to see if it is greater or equal to the value in G135. (If it isn't (in any of the comparisons) you get a value of 0.)
The next batch is more of the same - just algebra.
Finally, (1+Data!$AI$23)^(G135/4) takes the value on the sheet called Data in cell A23, adds 1 to it and raises the whole thing to the value of whatever is in cell after dividing that value by 4 (e.g., if the value in Da... Read more

3 more replies
Relevance 50.43%
Question: excel formula

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
 

Answer:excel formula

12 more replies
Relevance 50.43%
Question: Formula in Excel

I want to create a formula that would give me the following:if B5 is between 97-100 give me 25if is between 94-96 give me 20if is between 91-93 give me 15if between 87-90 give me 10

Answer:Formula in Excel

Thanks for telling us what you want to do. If you need any help with that, feel free to ask.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 50.43%

I have a list of sales invoices. For argument?s sake in column A I have the date paid , in column B I have the monetary value and in column C I want the monetary value if the invoice has not yet been paid.I need a formula along the lines of ?in cell C4 enter the value of B4 if there is no entry in A4 , if there is any entry in A4 do not enter anything in C4?.I hope I have explained my requirements (but maybe not as I am struggling to get this to work!). Any help will be very much appreciated. TIA.

Answer:Excel Formula Help Please

I need a formula along the lines of ?in cell C4 enter the value of B4 if there is no entry in A4 , if there is any entry in A4 do not enter anything in C4?. In C4 enter=IF(A4="",B4,"")

3 more replies
Relevance 50.43%

Hi,

Is it possible to have a formula count multiple criteria? for example, im trying to get the formula to count how many times it see the word H&SC but on a set day which is in another column.

I can do =countif(a1:a10, "H&SC") to count how many times it sees H&SC, but i dont know how to get it to check the day in column B1:b10
This is how my brain is thinking it.. if that makes sence? =countif(a1:a10, "H&SC)+countif(b1:b10, "17/06/2010")
Thanks in advanced,

Matt
 

Answer:Excel formula help.

6 more replies