Computer Support Forum

Solved: Excel formula length problem

Question: Solved: Excel formula length problem

My lady wife is having a small problem with a formula she is trying to use in an Excel spreadsheet.

She is trying to make a formula that she can copy and paste into various spreadsheets she utilises to organise her staff's workflow.
The five staff deal with about 30 suppliers and she wishes to be able to associate each supplier with a particular member of staff, so, for example, if column A is 'Suppliers'and column B is 'Staff Member', a formula using the IF function is placed in column B using the following syntax:
=IF($A1="aaaa","Rashmi",IF($A1="bbbb","Laura",IF($A1="cccc","Linda",IF($A1="dddd","Denise",IF($A1="eeee","Mark","")))))Click to expand...

This works just fine with only a few suppliers, but if the string is lengthened to more than about 9 suppliers, as here:
=IF($A1="aaaa","Rashmi",IF($A1="bbbb","Laura",IF($A1="cccc","Linda",IF($A1="dddd","Denise",IF($A1="eeee","Mark",IF($A1="ffff","Rashmi",IF($A1="gggg","Laura",IF($A1="hhhh","Linda",IF($A1="iiii","Denise",IF($A1="jjjj","Mark",IF($A1="kkkk","Rashmi",IF($A1="llll","Laura",IF($A1="mmmm","Linda",IF($A1="nnnn","Denise",IF($A1="oooo","Mark",IF($A1="pppp","Rashmi",IF($A1="qqqq","Laura",IF($A1="rrrr","Linda",IF($A1="ssss","Denise",IF($A1="tttt","Mark",""))))))))))))))))))))Click to expand...

-the formula ceases to work, the formula itself being displayed in the B column cells.

As this is only some 467 characters in length and Excel allows 1024, we are both confused as to what is happening here.

Anybody got any ideas?

Relevance 100%
Preferred Solution: Solved: Excel formula length problem

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: Solved: Excel formula length problem

11 more replies
Relevance 73.39%

i am creating a temperature converter from degree fahrenheit to celsius and vice versa. i want to make a column showing that whether this temperature is cold hot or medium. i want to write a formula in a cell which follows that if temperature is below 20 then display cold, if above 35 then display hot, if between 20 and 35, then display medium/warm. now the problem is that how to write three conditions in a single cell?
 

Answer:Solved: MS Excel Formula problem

moved to business applications

assume the temp is in cell A2

in cell B2 you can put

Code:

=IF(A2<20,"Cold",IF(A2>35,"Hot","Medium/Warm"))

you could
also use conditional formating to also change the fill colour of the cell - or the font colour

=A2<20 - format blue
=A2>35 - format Red
=AND(A2>=20,A2<=35) - format orange
 

3 more replies
Relevance 73.39%

Hi,
I want to set a formula in excel where I can add to a running total if a cell meets a certain criteria, for example. If cell A1 contains the word Yes then add 1 to B1, then say if cell A2 contains the word Yes then add 1 to B1 totaling 2. Hope this makes sense. Would you know of a formula to do this.

Regards
 

Answer:Solved: Excel Formula Problem

This sounds like a countif(range,criteria) application.

Try
b1= COUNTIF(A1:Axx,"Yes") replace xx with appropriate value.
 

2 more replies
Relevance 73.39%

I have fooled with this problem all day, and I am very sure it is something simple.

I am trying to calculate commission sale. Cells C5 through F5 are the input cells. The problem formulas are in cells C6 through F10.

I want to pay various % of the Gross based on dollars sold (Cells A6 through A9).

I have some formulas in place that work, but here is the problem. In Cell C6 through C9 I am fine as loong as gross sales do not exceed $17,000. When they go over 17,000 theformulas fails.

In Cells D6 through D9 I tried things differently. The result is that anything under $17,000 screws up.

What am I missing, can anyopne help? I have attached the file.

I am on a Mac, OSX 10.3.6, but have saved the attahced file for cross platform use.
 

Answer:Solved: Excel formula problem

Try this revised version and see if it works for you. The formulas are not pretty but they work
P.S. - I worked on the file in Windows so I don't know if you need to do anything to it in order to use it on the MAC.
Rollin
 

3 more replies
Relevance 72.57%

Hi,

I have created a basic spreadsheet for my home finances which takes a given balance, deducts expenditure and recalculates the new balance.

I have also formatted the spreadsheet so that if the balance drops below zero, the figures turn red to indicate a debit balance. So far so good!

I have two problems: firstly how do I incorporate any credit entries into the spreadsheet, ie at present if I pay in say £50 in the 'Receipt' column, I have to manually enter a formula asking Excel to add to, rather than deduct from the balance. Is there one formula asking Excel '
If entry is in the expenditure columns deduct from balance, if in receipt column add to balance

Also I also want excel to change colors back to black from red if balance goes over zero.

Hope I have explained the situation!

Thanks
Jampot
 

Answer:[SOLVED] Formula problem in Excel 2000

9 more replies
Relevance 71.75%

Hi,

I am having trouble getting results from a formula that is trying to return the sum of numerous values in a field once 2 conditions are met. I would say I have between novice and intermediate experience with excel formulas so I hope I can explain it clearly.

Worksheet 1: results

Worksheet 2: data
Column 1 (month): April, April, April, May, June, June, July, July, etc...
Column 2 (code): 4000, 6500, 4902, 4902, 3002, 4000, etc...
Column 3 (amount): £0, £0, £3598, £725, £0, £2212, £0, etc...

I want to get the sum of the amounts in Column 3 for the code in Column 2 in a specific month. For example, how much was spent in April for code 4902?

I am working with this formula
=SUM(IF(Worksheet2!$A$6:$A$245="April",IF(Worksheet2!$B$6:$B$246=4902,Worksheet2!$C$6:$C$246,0),0))

However, this is returning errors.

Can anyone help solve this one, perhaps with a simpler formula?

Thanks
 

Answer:Solved: Excel 2007 - Conditional SUM formula problem

16 more replies
Relevance 68.47%

the answer to this might of been found with a search but hey i'm lazy today and wanted to see who the excel masters are

using excel 2007, cell a1 contains an array formula (CSE) like so - {=MAX(LEN(A2:A470000))}
cells a2 through a470000 contain dates such as
12/12/2011
etc, you get the idea, i need the formula or something else entirely to return 10 not 5

thanks in advance
 

Answer:Solved: excel length of date field

i'll answer if myself thanks to another web site, Mike these guys are slackin' 'round here it looks like

=MAX(LEN(TEXT(A2:A470000,"m/d/yyyy")))

exit celll with ctrl, shift, enter
 

1 more replies
Relevance 64.78%

need 4 sasheslength of material is 300 cmwidth of material is 150 cmwidth of sash is 50 cm3 sashes per length of materialI need to have 600 cm of material2 sashes left over

Answer:Formula to calculate the length of material required

You might want to consider using this formula, which answers your original question:"therefore I need a formula to give me the required 6 metres"=ROUNDUP(A1/3,0)*3Enter the number of sashes required in A1.Some examples of the results
Sashes Metres
1 3 1/3 = .333, ROUNDUP(.333,0) = 1, 1x3 = 3
2 3 2/3 = .666, ROUNDUP(.666,0) = 1, 1x3 = 3
3 3
4 6 4/3 = 1.333, ROUNDUP(1.333,0) = 2, 2x3 = 6
5 6
6 6
7 9
8 9
9 9
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

9 more replies
Relevance 62.32%

Hi, Since I always find technical help on here thought I'd see if anyone can help me with an excel formula as very, very rusty on this program.

I need to link a cost value to a text drop down box depending which option has been selected
Colums I Need are
Item Unit Price Quantity
The "Item" column has dropdown options, A,B,C,D,E, If Option A is selected I want the 'Unit Price' column to auto populate with the related "Unit Price" number and the same with the other options.
Hope that explains it, still a bit muddled in my head as been reading formula after formula today.
 

Answer:Solved: Excel Formula

16 more replies
Relevance 62.32%

Hi,

May I please ask for some expert advice with a formula? I've been trying to come up with something, but can't figure it out.

I'm trying to populate cells in column C with a rate that is based on columns A and B.

This is what I'd like to say:
If B2 equals USD, then add 1 in cell C2. If B2 equals AUD, and A2 equals 2013, then add 5 in cell C2. If B2 equals AUD, and A2 equals 2014, then add 7 in cell C2. If B2 equals CAD, and A2 equals 2013, then add 8 in cell C2. If B2 equals CAD, and A2 equals 2014, then add 9 in cell C2.

And my table looks something like this:

A B C
2 2014 AUD Need formula here
3 2013 USD Formula will be copied here
4 2014 CAD Formula will be copied here

Thank you very much in advance for your help.
Fern.
 

Answer:Solved: Need help with Excel formula please

9 more replies
Relevance 62.32%

Column A would be company, column B would be mail type, and column C would be the address. The numbers just represent the columns but would have no reference to the spreadsheet.
1 abc
2 abc billing po box 1
3 abc mail 801 smith street
4 acb mail 102 block
5 acb billing po box 3
6 acb

I want to be able to do a formula that will look at column a and b to get the address based on mail or billing how do i get this to work.

Thanks
 

Answer:Solved: Excel 03 formula Help

16 more replies
Relevance 62.32%

Running XP Professional, Excel '97 - yes I know it's VERY old! (I am also running Excel on my new IMAC, but can sit in my comfy chair with my old, pc laptop)

I am running a sales event where several sales reps are selling samples. The columns are the different reps, the rows are the different sample items. I would like to be able to enter the price in the rep's column AND total the customer's purchases while entering the prices only once. I would like a formula that says if the value in a cell is >$0, enter that value in the column where I calculate the customer's purchases. After that customer's purchase is complete, I want to erase the prices in the customer calculation column, while leaving the prices in the reps' columns. At the end of the event, I need to be able to divy up the money based on the totals of the reps' columns. Thanks.

if (d2:g24>0, .......................
 

Answer:Solved: If formula in Excel

16 more replies
Relevance 62.32%

Hi all,

I have small issue.
I pull record for almost 1000 ppl every single day.
In excel cell A will be the Name of the user, which is in this pattern (Last name, First name).
And cell B is the email address for the user, which is in this pattern (First name.Last [email protected]).
What i need to do is i have to manually type the email address in cell B.
Is there a way by which i make it automatic. So that when ever i (Last name, First name) in cell A it it should auto-populate
the email address in Cell B.

Any help will be much appreciated as this manual work is a pain in the wrong place everyday

I have attached the example spreed sheet.
 

Answer:Solved: Excel Formula

7 more replies
Relevance 62.32%

Hello,

I would like to do the following:

IF CEL A1 contains value "per ton" do formula: =IF(A1<70,"70", A1)

ELSE IF CEL A1 contains value "per cbm" do formula: =IF(A1<19,"19", A1)

How should I do this?

Thanks!
 

Answer:Solved: Excel IF / ELSE FORMULA

15 more replies
Relevance 62.32%

I am in need of help with a formula. I have a set date that a tast occured. The next task must occur within 7 days of that first date. So I have a window of +7 days. When the actual date the next task occurred is outside of that window, I would like the cell or text to change colors.

Example 1:
Task Window is +7 days

A1 = 24 Jan 2012
B1 = 25 Jan 2012 (First possible date task can occur. Formula used "=A1+1"
C1 = 31 Jan 2012 (Last possible date tack can occur. Formula used "=A1+7"

I would like to enter the actual date the task was performed into D1. When that date is outside of the range from B1 to C1, I would like the font or the cell to change color.

Example 2:
Task Window is -7 days, +21 day

A2 = 24 Jan 2012
B2 = 17 Jan 2012 (First possible date task can occur. Formula used "=A2-7"
C2 = 14 Feb 2012 (Last possible date tack can occur. Formula used "=A2+21"

I would like to enter the actual date the task was performed into D2. When that date is outside of the range from B2 to C2, I would like the font or the cell to change color.
Can you help?

Thanks,
MaryKay
 

Answer:Solved: Excel Formula Help

8 more replies
Relevance 62.32%

Hi!

This is going to be a bit tough to explain and is not really all that Excel related - more of a math question:

I have to be able to assign a (single) unique number (no matter which numer) to a unique set of whole numbers.

That is, I need to distinguish between, let's say, {2,3,6} and {4,7}. So I'm trying to find a formula that will always give me a unique number for a uique set. (The sets are random, but only whole numbers 1-9 are used ( kind of like sudoku)).

I thought this would be an easy problem, but I have yet to find a simple formula that will give me such a number. Simply summing the numbers won't work of course- in the example above both sums are 11 (not unique). I have also tried summing the squares of individual numbers, standard deviation, variance... and all of these fail to give a unique number for each unique set.

Is there such a formula? Any idea is a welcome!
 

Answer:Solved: Excel formula

11 more replies
Relevance 62.32%

I need some help with this formula;
=ROUNDUP(((A1+A2)/450)+9,0)
A1 450
A2 450
So basically should be 450+450=900, 900/450=2, 2+9=11
But I get 10
Thanks
 

Answer:Solved: Excel Formula Help

On my test.
=ROUNDUP((A1+A2),0)/450+9 will equal 11.
=ROUNDUP(((A1+A2)/450)+9,0) = 11
Round function is for decimals
 

2 more replies
Relevance 62.32%

i'm trying to formulise a cell based on whether another cell contains a "Y" or a "N":

IF cell J5 contains a Y i want cell K5 to = SUM(H5:I5) if cell J5 contains an N i want K5 to = H5.

I've tried SUMIFS formula but it doesn't seem to work. any ideas on what the formula should be?
 

Answer:Solved: excel formula

Hi Lyndsay_k,

This is what you ask for, in cell K5 put
=IF(J5="Y",SUM(H5:I5),IF(J5="N",H5))

But I suspect that what you simply want is:
If J5 is Y then K5 is the sum of H5 to I5,
otherwise it is H5 regardless.
which is simpler as, in K5 put
=IF(J5="Y",SUM(H5:I5),H5)

lol
Hew
 

3 more replies
Relevance 62.32%

I want to query a cell and if it is equal to or less than the numeral 2, have another cell display the text Minimal, if it is greater than 2 but less than 5 have said cell display Marginal and if 5 or over display Disaster Waiting to Happen...is this feasible?
 

Answer:Solved: Excel IF formula

6 more replies
Relevance 62.32%

I want to create a simple workseet where I track down my productivity based on the number of words I translate per 30 and 60 minutes. That way, I can know how much money I'm making (and not making) on a real time basis. So, I have something like this:

AVERAGE PER HOUR 208.00
AVERAGE PER 1/2 HOUR 416.00

6.00 382 ---
6.26 576 194
7.01 760 184
...

Colum 1 is the time. Column 2 is the number of words I have completed IN TOTAL. Colum 3 would be the number of words I translated each 25/30 minutes (I'm using the Pomodoro technique) .

So in this case, I started at 6.00pm with 382 words already translated. By 6.26, I had 576, so I want to substract 576 - 382 to get the number of words I had translated in that time-lapse. Then I want to use those results, from column 3, to get my total average.

I've almost got it right each time, but since I copy/pasted a simple subtraction on column 3, I get a lot of zeros on the empty rows on column 2, which screws up my averages.

My input will be only the time and the number of words translated each time-lapse, I want the rest to be done automatically. Is there an simpler way to do this? I hope I explain myself enough, I'm not good with numbers.

Thanks.
 

Answer:Solved: Help me out with an Excel formula

10 more replies
Relevance 62.32%

I know this is probably very simple, but I am tryng to come up with a sum formula that will sum all the data in a column as long as it meets specific criteria in another column.

For Example:
Column A
123
456
789
563
654
123
123
987
159
235
123

Column B
255
300
99
45
632
12
3
1
789

I would like to sum all of Column B (excluding the values where Column A is equal to 123)

Can this be done?
 

Answer:Solved: Excel Formula -> Sum

6 more replies
Relevance 62.32%

Can anyone tell me what the error is in this formula:

=IF(OR(State="IL",State="illinois"),(=PRODUCT(Subtotal,0.0725),"0")))

State refers to a cell ,g17, that I have named State.
Subtotal refers to a cell, h37, that I have named Subtotal

I am trying to get a sales tax amount that equals the subtotal times .0725, if Il or Illionois are entered in the state cell, if anything else is in the state cell then sales tax should equal 0.

My brain can almost grasp the problem, then I lose it....
 

Answer:Solved: Excel Formula

Aha!!

If I remove the equals sign before "Product", and the parenthesis that surrounded that part it works.

i.e.:

=IF(OR(State="IL",State="illinois"),PRODUCT(Subtotal,0.0725),0)

Silly me....
 

1 more replies
Relevance 62.32%

I have a formula in a cell to use the FIXED function. My formula looks like this:

=If(B9>5,Fixed(C9*L9),C9*L9) -> The result comes out to be 6

This part works fine. The formula above is located in cell E9.

I then have another formula in a cell (L9) that references the cell E9. However, the result that is diplayed in L9 is 6.3.

How can I get the formula in L9 to see the actual displayed value of 6 that is found in E9?
 

Answer:Solved: Excel Formula(s)

9 more replies
Relevance 62.32%

I am sure this is easy but I can't work out how to do it.
I have a spreadsheet with Cols A to K
If there is an entry in Col B it will be a ref number for an existing client.
I then want Excel to refer to my client list (on another Excel sheet) and enter the values from Cols C,D,E and F into those cols on the new sheet.

Let me explain. The client list contains Col B ref Number, Col C First Name, Col D Last Name, Col E Address, Col F Email

Now when one of these clients makes a purchase I would like to just enter the ref number on the new worksheet and have it fill in the details
If there is no ref number then I want Excel to do nothing so I can enter the new information as needed.

But how?
 

Answer:Solved: Excel. Help with formula

11 more replies
Relevance 62.32%

I'm having trouble creating a formula and would really appreciate any help I can get. I think I should be using the CONCATENATE function but I'm really not certain. I did the best I could to recreate the table below, but if you have any clarifying questions please let me know:
For each Name, I need an output the CONCATENATEs the name of the column if there is an X in that column, seperated with semicolons (The Desired Output column).
_______A__B__C__Desired Output
NAME1_X___X______A;B
NAME2_X___X__X____A;B;C
NAME3_X______X____A;C

I really do appreciate any help you can give,
Tim
 

Answer:Solved: EXCEL Formula

Hi Tim, welcome to TSG.

Someone's going to come along soon and give a cleaner way of doing this, I'm sure, but to get you started, you could try something like this:

=CONCATENATE(IF(B1="X","a;",""),IF(C1="X","b;",""),IF(D1="X","c",""))

The problem with it is that you'll have stray semicolons if there's nothing in column C. But give it a try and see if it gets you close to what you're going for.
 

3 more replies
Relevance 62.32%

Hello guys.

Greetings from the South Pacific Islands.

I have a question regarding a formula in Excel.

I need a formula which will calculate the AER of an investment. In other words
if I invest $ 10,000 and after 1,000 days the investment is
worth $11,000 what is my AER, in other words what is my annual
increase in percentage terms ?

My best regards.

Natty.
 

Answer:Solved: Excel formula

Hello there!

Well, if you have your Investment in B1, number of Days in B2, Final Amount in B3, we can use the formula ..

=INTRATE(TODAY(),TODAY()+B2,B1,B3)

Which returns 3.66%

Is this what you're looking for?
 

2 more replies
Relevance 62.32%

Hi. I need help with the following formula:

=PRODUCT($F$6,G15)

Where F6 is a set value and G... is a series of numbers I'm updating every so often. The problem is that if I paste that formula in several cells, they are filled up with the F6 value. Is there a way to have those cells empty if the G... value is empty?

Thanks.
 

Answer:Solved: ISO help with an Excel formula

try
=IF( G15="","",PRODUCT($F$6,G15))
 

3 more replies
Relevance 62.32%

I'm looking for some help from you kind people! Hopefully one/some of you can assist!

I'm looking for a formula that will compare on cell column A to all of column B and pick return a true/false -- yes/no answer. It is numbers, but it's formatted in text as to not lose the leading zero.

Thanks for any assisance you can offer...

--Mz

 

Answer:[SOLVED] Excel formula!!!

8 more replies
Relevance 62.32%

i am trying to come up with a formula for the following data.

A B C
1 300.00 142 =(100*A1)
2 250.00 75 =(75*A2)
3
i would like to be able to multiply the amount in column "B" up to 100 by the amount in column "A"
the way i do it now is column "C" i put in the formula (100*a1)
the problem is that sometimes the value in Column B is less than 100. For example - line 2
It is a large spreadsheet, so i would like one formula to apply. now i have to enter a different formula for values in column B that are less than 100.

i tried an IF formula but it would not work. my logic was. if = 100 then mulitply that amount by col. A if less than 100 then multiply that value by col A.
any ideas
 

Answer:Solved: Excel formula help!

8 more replies
Relevance 62.32%

Hi

Am having a mind blank

What formula would I use in D4 to get the following result

If Sum of B4 is greater than C4 then D4 = B4/C4, if Sum of B4 is less than C4 then D4 = C4/B4

Any help would be greatly appreciated
 

Answer:Solved: Excel Formula Help

7 more replies
Relevance 62.32%

I'm not very good with formulae and would appreciate some help.

In cell U7, users will enter a customer account number. This can be any number of digits between 3 and 8. For example, 123, 12345, 1234567 etc.

The account number needs to be copied, as individual digits, to range AE7:AL7. The problem is that if there are less than 8 digits, missing digits must be filled with a zero, and must be filled starting at AE7. So, if an account number has only 7 digits, AE7 must be a zero, if an account number has only 5 digits, AE7, AF7 and AG7 must be a zero, and so on.

I've tried various combinations of MID, LEN and RIGHT but I can't quite get this right.

In summary, the formula needs to test the number of digits input in U7, extract each digit, and if less than 8 digits, it needs to insert zeros starting at AE7, then continue inserting each digit thereafter.

This is not my sheet, so I'm stuck with the current design.

Thanks for any assistance.
 

Answer:Solved: Excel MID Formula Help

Hi Glas!

AE7
=IF(LEN(U7)<8,0,MID(U7,LEN(U7)-7,1))

AF7
=IF(LEN(U7)<7,0,MID(U7,LEN(U7)-6,1))

AG7
=IF(LEN(U7)<6,0,MID(U7,LEN(U7)-5,1))

AH7
=IF(LEN(U7)<5,0,MID(U7,LEN(U7)-4,1))

AI7
=IF(LEN(U7)<4,0,MID(U7,LEN(U7)-3,1))

AJ7
=MID(U7,LEN(U7)-2,1)

AK7
=MID(U7,LEN(U7)-1,1)

AL7
=RIGHT(U7,1)
 

3 more replies
Relevance 62.32%

Howdy all

Following on from Bomb's great macro this morning that sorted my thousands of users and their access levels (I found some had up to 26 different access levels), I've come across another problem that I can't solve.

The results have come out as per the sample below (I tried to upload the file but it's too big at 4 mb - I can send it to anyone if desired), which is exactly the results I needed, but I've found another problem which is the fault of the originating database (not Bomb's macro!). The access groups aren't in ascending order which I require and I can't for the life of me work out how to get all the access rows listed in ascending order without doing them one at a time (I tried using the sort function and it didn't really work - it changes everything but not actually into ascending order except for the first row).

Can someone much smarter than me help me out again? You can have my first born (although she's a little quirky).

USER ACCESS
11 195 194 30
12 0 62 70 333
43 68
55 65 204 208
59 208 203
66 312 311 334 378 275 279
69 203 34 355
71 204 65 34
77 34 204
83 187
87 34 204
88 34 204
 

Answer:Solved: Further Excel Formula Help!

6 more replies
Relevance 62.32%

Request help on formula to hide if there is no value in referred cells.

Column A=date
Column B = Credit
Column C=Debit
Column D=Balance (i.e. D2+B3-C3)
Balance is required for whole month.
If there is no input either in credit or debit column Balance Column is to show blank

Please see attachment.
 

Answer:Solved: Excel formula help req.

6 more replies
Relevance 62.32%

I am trying to come up with a formula using an IF statement. I have ten cells that I need to check if the cell contains a certain text. I have tried to use the AND function within the IF statement, but the problem is that 5 cells will contain the text needed, and the other 5 are blank. Since the other 5 cells are blank, the IF statement is coming out false.

Is there a way to write the IF statement that will ignore the blank cells? However, some or all the blank cells may need to be filled in later.
 

Answer:Solved: Excel Formula

16 more replies
Relevance 62.32%

I never used Excel, but my wife asked me to help her with a homeschooling grade book in Excel.
I think I've done well. Except for the last formula which keeps coming up as #DIV/0. I think it's because I can't divide by 0. Any tips on how to correct this would be awesome.

It is a weighted grade book
C7=(M7/M6*.1)+(U7/U6*.15)+(AC7/AC6*.15)+(AK7/AK6*.2)+(AQ7/AQ6*.4) is the last formula where I'm having trouble

and these are samples of the fist operation source values
M6=SUM(E6:L6) total possible blank cells
M7=SUM(E7:L7) total received blank cells

thanks in advance
 

Answer:Solved: Excel Formula Help

6 more replies
Relevance 62.32%

HI, Im a super mega noob in Microsoft Office Excel, I would try to make this simple I want in a single cell for example C2. I want in C2 the total of All Columm A - All Columm B. How I do that?, what formula I have to use?, I only can make it work for a single cell (example A1-B1 = C1). Running Office 2010.
 

Answer:Solved: Excel Formula

9 more replies
Relevance 62.32%

Dear all,

Please find the attachment, Sheet (Zapmotail) Column N contains Either "GSC" or "N/A"

All I need is :

If N2,N3,N4 Etc... contain "GSC", the whole row has to be copied to Sheet "Unique"
If N2,N3,N4 Etc... contain "N/A”, the whole row has to be copied to Sheet "Not Under Scope"
thanks
pkn
 

Answer:Solved: Excel formula help

7 more replies
Relevance 62.32%

Could someone please help me out with this formula? Excel is saying that it is incorrect, and I'm not quite sure how to fix it. Here it is: =IF(C2<>C3,"",IF(AND(H2<>"ETA",H2<>"ROM"),"",IF(H2="ETA",L2=L3),"",IF(H2="ROM",L2=L3),"",1)).

I'm attaching that spreadsheet that I'm trying to apply it to. The end result that I am looking for is that if the reason equals ETA for a person that their job title is the same for that row and the row below it. The same goes for reason code ROM.
Thanks.
 

Answer:Solved: excel formula

In your file, all the combinations lead to a result of "", so you see nothing. If you try it out by replacing the 4 "" responses with something else, like "2", "3", "4" and "5", you will see values appear in the column - I did and got the following (from top to bottom) 4 4 3 2 3 4 3 3 3 4 3 3 3 3 3 3 3 3 3 3 3 3 3 2 3 3 3 3 2
 

1 more replies
Relevance 62.32%

Howdy all

I have an excel sheet with an access authority in column A and a user ID in column B. If a user ID has more than one access authority they will be listed on multiple rows, as follows:

ACCESS USER
A 1
B 1
C 1
D 1
A 2
B 2
A 3

I have a list with 18,000 users and I am trying to perform two functions with this data but have had very limited success so far.

Firstly I want to create a new column that lists all the access authority any user has (such as follows)

ACCESS USER COMBINED ACCESS
A 1 A B C D
B 1 A B C D
C 1 A B C D
D 1 A B C D
A 2 A B
B 2 A B
A 3 A

I couldn't get a single formula to do everything I want so I've tried to split this into several formulas so I can firstly identify which users have more than one level of access (using an IF & COUNTIF formula) and then deal with the results. This bit does work OK. I've then tried to use this identifier with a number of different formulas to try and concatenate the relevant access levels into a single field. But it doesn't work properly (or at all most of the time) - I'm presuming I can't actually concatenate fields ONLY if the user ID is the same (which is what I think I want to do). I'm hoping soemone here can actually understand what I'm trying to say, and either point me in the right direction or tell me to sod off if it can't be done!

Secondly I ultimately want to strip out all multiple instances of each user so I'm left with only a single user and the re... Read more

Answer:Solved: Excel Formula Help

13 more replies
Relevance 62.32%

I am trying to come up with a formula that is able to find a sem-duplicate number in a column, and move it to another column.

For Example:

12345
12346
12347
12345A
12348
12349
12351
12352
12346A2

I will need to take 12345, and place it in Column N, and place 12345A in Column O. 12346 will go in Column N, and 12346A2 will go in Column O.

Is this possible?

Let me know if I need to clarify anything.
 

Answer:Solved: Excel Formula

16 more replies
Relevance 62.32%

I am trying to use mround AND set a maximum number. The mround formula is

=MROUND(B4/16,1)*8

I want to set the maximum value to be 96. I have tried numerous options like if, and, celing, etc. None have worked. Can anyone help? Thanks.
 

Answer:Solved: Need help with excel formula

7 more replies
Relevance 62.32%

I need some help getting a rate per hour on my sheet that I have set up.

Column H has the formula - "=E3*F3"
Column I has the formula - "= E3*G3"

Column J & K is what I need to know is correct.

Column J (CH#6) has the formula - "=H3+I3/45*60"
Column K (CH#7) has the formula - "=H3+I3/31*60"

45 and 31 equal current cycle time in minutes CH#6 and CH#7 takes to cycle. I am wanting to know how many shots per hour each CH# gets with the current cycle time.

I have attached a sample of my sheet.
 

Answer:Solved: help with formula in excel

If you're looking for say 320 in J3, then you need to add some brackets to force something called operator precedence:

=(H3+I3)/45*60
 

1 more replies
Relevance 62.32%

Once again I am stuck with a formula and hope for some kind soul to help me.
I clarified a similar issue a few months ago but can't quite recreate the result.

This time it's even easier (at least I think) - I want to use one column to either enter a digit or leave it blank (I do that manually). Another column will show with conditional formatting if stock levels are falling below thresholds, ie the cells don't contain any formula directly but only via conditional formatting. The formatting only provides for a colour background.

My aim is to override the colour background if the cell in the earlier column shows a digit. What should the formula be for something like : if cell k16 shows 1 then ignore conditional formatting in cell N16 or O16.

My intention is to have this row N show a black (alert) if attention is necessary but only for those stock items that are still relevant. For discontinued items (items 1-14) I can prevent the alert by referring to the other column (K).
 

Answer:Solved: Excel Formula Help

I have added this in your function

=IF($K$16<>1, ((Z16-AO16)<$O$1*AO16))

then added an additional function
=$K$16=1

see attached

i think that works
 

3 more replies
Relevance 62.32%

I have the following formula. What I need to do is make a change to this formula.
=IF(ISERROR(SUM(L4-H4)/H4),0,SUM(L4-H4)/H4)
Basically I need if H4 is blank and L4 is blank return 0 else if H4 is blank and L4 has data return 1, else if H4 has data and L4 has no data return -1 else if both H4 and L4 have data then SUM(L4-H4)/H4)

Bascially I am co,paring data from this year to last year aand trying to return the % of change in sales. We want to see a 100% if there were sales this year but none last year and if there were sales last year but not this year them -100%.

I hope that make sense.......
 

Answer:Solved: Excel Formula

6 more replies
Relevance 62.32%

I'm trying to make a formula for my grade sheets so I don't have to manually go in and type 650+ grades when grades close. Without going into too much detail- if the child's grade is higher than 1 I give them an "S." If it's a 0 or lower, they get an "N."

The forumula I tried was =IF(cell>0, "S", IF(cell<0, "N"))

What did I do wrong? I had a much more complicated one last year with 5 grades that worked- guess I missed something.

Thanks
 

Answer:Solved: Need help with an Excel Formula

7 more replies
Relevance 62.32%

need help fast - EXCEL 97 formula
formula entered =sum(h4:h5)<=30
results in answer of TRUE or FALSE
I need the actual number displayed
 

Answer:{Solved} excel 97 formula

6 more replies
Relevance 62.32%

I am hoping someone can help me with this, as I am most certainly lost. I am attempting to create a spreadsheet that allows me to enter ASVAB test scores and physical condition data (on sheet 1) for an applicant that then correlates to related cells (on sheet 2) and then if certain conditions are met, a Cell on (sheet 2) will then show results of either: "QUALIFIED" (cell filled in green) or "NOT QUALIFIED" (cell filled in red).
I am trying to see how to create this formula correlating the data from the multiple cells across two sheets with the results highlighting the one cell in one of two colors with the related text above.

Here is an example of what I am working with:

Using Data entered into:

C10 (sheet 1) => F4 (sheet 2)
and J4(sheet 1)=< P4(sheet 2)
and J7(sheet 1) =< Q4(sheet 2)
and J10(sheet 1) =< R4(sheet 2)
and M4(sheet 1) =< S4(sheet 2)
and M7(sheet 1) =< T4(sheet 2)
and M10(sheet 1) =< U4(sheet 2)

with results ending up in C4 (sheet 2) that display either:"QUALIFIED" (cell filled in green) or "NOT QUALIFIED" (cell filled in red).

EXAMPLE 2:

Using Data entered into:

F4 (sheet 1) => I7 (sheet 2)
C4 (sheet 1) => J7 (sheet 2)
F16 (sheet 1) => N7 (sheet 2)
and J4(sheet 1)=< P7 (sheet 2)
and J7(sheet 1) =< Q7 (sheet 2)
and J10(sheet 1) =< R7 (sheet 2)
and M4(sheet 1) =< S7 (sheet 2)
and M7(sheet 1) =< T7 (sheet 2)
and M10(sheet 1) =< U7 (sheet 2)

with results ending up in C... Read more

Answer:Solved: Excel Formula Help

C10 (sheet 1) => F4 (sheet 2)
and J4(sheet 1)=< P4(sheet 2)
and J7(sheet 1) =< Q4(sheet 2)
and J10(sheet 1) =< R4(sheet 2)
and M4(sheet 1) =< S4(sheet 2)
and M7(sheet 1) =< T4(sheet 2)
and M10(sheet 1) =< U4(sheet 2)Click to expand...

you say AND - so you should be able to use the AND function and conditional format

with results ending up in C4 (sheet 2) that display either:"QUALIFIED" (cell filled in green) or "NOT QUALIFIED" (cell filled in red).Click to expand...

=Sheet2!C3

=AND (
sheet1!C10 >= sheet2!F4,
sheet1!J4 <= sheet2!P4,
sheet1!J7 >= sheet2!Q4,
sheet1!J10 <= sheet2!R4,
sheet1!M4 <= sheet2!S4,
sheet1!M7 <= sheet2!T4,
sheet1!M10 <= sheet2!U4
)

that will give true or false

so now we add an IF

=IF ( the and statement, "QUALIFIED" , "NOT QUALIFIED")

so should look like this

Code:

=IF(AND(Sheet1!C10>=Sheet2!F4,Sheet1!J4<=Sheet2!P4,Sheet1!J7>=Sheet2!Q4,Sheet1!J10<=Sheet2!R4,Sheet1!M4<=Sheet2!S4,Sheet1!M7<=Sheet2!T4,Sheet1!M10<=Sheet2!U4),"QUALIFIED","NOT QUALIFIED")

and then use conditional format to set the colour based on qualified or non qualified

looked at your sheet wasnt sure where to put the formula as no sheet 1 or sheet 2

so i attached a sample here

and to test just change cell C10 to 1 or -1
as 1 will be true for the AND statement and -1 false
for C7

AND(
sheet1!F4 >= sheet2!I7,
sheet1!C4 >= sheet2!J7,
shee... Read more

3 more replies
Relevance 62.32%

Hi I created 3 formulas and I wanted to put them together. I tried doing it with 'OR' condition but couldn't succeed.

Formula 1:
- if B3 (level) is 1, 3 and D3 cell is DD and Y or N or X or NP

=IF(DATA!B3={1,3},IF(AND(DATA!D3="DD",DATA!FF3="Y"),"Good",
IF(AND(DATA!D3="DD",DATA!FF3="N"),"N",
IF(AND(DATA!D3="DD",DATA!FF3="X"),"X",
IF(AND(DATA!D3="DD",DATA!FF3="NP"),"NP",
IF(AND(DATA!D3="DD"),DATA!FL3,""))))))

Formula 2:

=IF(DATA!B3={2},IF(AND(DATA!D3="DD",DATA!FF3="Y"),"Good",
IF(AND(DATA!D3="DD",DATA!FF3="N"),"N",
IF(AND(DATA!D3="DD",DATA!FF3="X"),"X",
IF(AND(DATA!D3="DD",DATA!FF3="NP"),"NP",
IF(AND(DATA!D3="DD"),DATA!FL3,""))))))

Formula 3:

=IF(DATA!B3={2},IF(AND(DATA!D4="DD",DATA!FF3="Y"),"Good",
IF(AND(DATA!D4="DD",DATA!FF3="N"),"N",
IF(AND(DATA!D4="DD",DATA!FF3="X"),"X",
IF(AND(DATA!D4="DD",DATA!FF3="NP"),"NP",
IF(AND(DATA!D4="DD"),DATA!FL3,""))))))
There are couple of more, but once I know how to add the OR condition I could add other once.
I could add two formulas together but not the third one I get an error: The specied formula can... Read more

Answer:Solved: Excel formula help

16 more replies
Relevance 62.32%

I think this can be done, I just don't know how to work Excel that well.

I have a spread sheet with one column that has our 'rate'. We just found out the rate is going up 2 % then rounded up or down to the nearest $5 dollars. Is there a way to enter a formula to the 7 page spread sheet that is already typed to do this figure?

Thanks and as always kindest regards,
Christine
 

Answer:Solved: Help w/Excel formula

11 more replies
Relevance 62.32%

I am getting a "DIV/0" on the following formula:

=AVERAGE('Cadell:Assoc 22'!C10)

I would like to have it display nothing in the cell if it is going to return "DIV/0". I tried this:

=IF(AVERAGE('Cadell:Assoc 22'!C10)=ISERROR,"",AVERAGE('Cadell:Assoc 22'!C10))

It did not work. Can someone give me the right way to do this?
 

Answer:Solved: Excel formula

6 more replies
Relevance 62.32%

Hi all,

My Excel skills are a bit rusty and hope you guys can help me out.
I am experimenting with a simple spreadsheet for the transport company where I work for.
Sheet 1 shows Locations with 5 columns
Column B Customer (user input)
Column C and Pallet spaces (user input)
Column D (at present not relevant)]
Column E Reg (user input)
Column F I would like to create a formula that looks at the vehicle reference sheet at the capacity of the corresponding registration and deduct the pallet spaces for the relevant vehicle
Each vehicle may travel to several customers and I would like to monitor how much ‘free pallet space’ they have.
(the same goes for location 2)
If I can get this to work, it potentially replaces the old fashioned white boards and instead we project the spreadsheet on the wall.

Thanks for the help guys!
 

Answer:Solved: excel formula

16 more replies
Relevance 62.32%

Can someone help me come up with a formula or VBA that will calculate mileage.

For Example:

Miles: 100

per mile: 13.5 cents
 

Answer:Solved: Excel Formula/VBA

8 more replies
Relevance 62.32%

Hi ! I want to place a formula in Q2:Q90 that says if H2:H90 is X and P290 is W than Q2:Q90 is 2 times O2:O90 thanks for any help this is important to me hope to here from some one soon.
 

Answer:Solved: Excel formula

16 more replies
Relevance 62.32%

I am trying to create a formula that will change the way a date appears in a cell.

For example, here are some date values:

1/2/2008
9/26/2008
10/8/2008
10/14/2008

I need an a formula to place a 0 in the month and day of the dates that are sing numbers. If the month or day is a double digit, then it needs to remain the same.

So, the examples values above would end up lik ethis:

01/02/2008 (both month & day changed)
09/26/2008 (month changed)
10/08/2008 (day changed)
10/14/2008 (same as above)
 

Answer:Solved: Excel Formula

Have you looked at the format / date options? There are a lot of them, including what you are asking for (I think ....this is from memory).
 

2 more replies
Relevance 62.32%

Hi all,

Having some trouble making this work, so here's the deal. I have a spreadsheet to track people who have signed up for some events we're hosting in October. There are three events, each one costs $30. If they attend all three events, they get a free T-shirt if they want one. If they only attend one or two events and still want a T-Shirt, then the shirt is $10 extra. I need a formula to calcluate the total cost depending on what they sign up for and if they want a T-shirt. I have cells to indicate Yes or No for each event, and a Yes or No for the T-shirt. Please let me know if I need to explain more, but I think it covers it.

Thanks,

Vince
 

Answer:Solved: Need help with an Excel formula

Never mind, I finally figured it out.
 

2 more replies
Relevance 62.32%

Hi...I'm working on a spreadsheet in which I am trying to sum the hours worked by employee, by activity. So basically, how many hours has a particular employee spent on a particular.

I've attached an example of the spreadsheet from which I'm working. Please help

Thanks,

Mario
 

Answer:Solved: Excel Formula Help

13 more replies
Relevance 62.32%

Hi guys,

This request will be a easy one for you guys.
Can any1 provide me formula for excel so that when i paste a name in Cell A, Cell B should auto populate to the email id.
Example: Cell A ( George Memphis ) Cell B should be ([email protected])

i have also attached a example spreed sheet.
Please guys any help will be very help full. As this is done for over 500 users every 3 hours at work.
 

Answer:Solved: Formula for excel

8 more replies
Relevance 62.32%

Is there a way to do the following in Excel.

I have a cell that contains data that is the sum of 2 or 3 other cells. Then that cell (the sum cell) is used in another cell.

What i want to do is set it so that if the value of the sum cell is less than zero (if it is a negative number) to just treat it like a zero.

Is this possible? How so?

Thank you.
 

Answer:Solved: an Excel formula

11 more replies
Relevance 62.32%

Howdy Excel Experts!

Been a long long time since I've dealt with if and other statements ... hehehe

anyways, I'm almost done with the problem (attached) for my wannabe accountant friend. she just needs help on formulas as it is beyond what they study.

Need the formula for the following:
the average length of stay per room type
the average number of visitors per room type
and the base income per room (i.e., length of visit multiplied times the daily rate) during a specified period of time.

Tried several things but I was stumped, that's why I am here

[Edit] by the way, the problem itslef is on the second sheet named "Data". ty
 

Answer:Solved: Need Excel Formula...

6 more replies
Relevance 62.32%

Hi
I hope someone can help with this as I've tried everything I know.

This is the outcome I am trying to achieve.
if A is a negative number, show the result of A plus B
if A is a positive number, show 500

This is the formula I presently have and it works until the A1 is both <B1 and positive
=IF(A1<B1,A1+B1,IF(A1>B1,500))

All help much appreciated
 

Answer:Solved: Help with an Excel formula

=IF(A1<B1,A1+B1,IF(A1>B1,500)) does not compare for if A is negative, it compares for if A is less than B.
Try
=IF(A1<0,A1+B1,500)

And welcome to the Forum!
 

3 more replies
Relevance 62.32%

Can anyone help me figure this out? I am running out of time for a dead line and this is the last stumbling box I have.
I have 16 rows and at the end of each row is a total in the last row is the bottom of the column that totals the rows above it. If any of the 16 rows are <40 I want it to total them in the bottom row if they are >40 I dont want a total in the bottom row and I would like it to highlight the box if there is a sum in the bottom row and not highlight if it is empty.

I am using Office 2007
 

Answer:Solved: Need help with a formula in Excel please

I am using office 2003. I can get more help this site. the end of each row is a total in the last row is the bottom of the column that totals the rows above it. If any of the 16 rows are <40 I want it to total them in the bottom row if they are >40 I dont want a total in the bottom
 

3 more replies
Relevance 62.32%

Hi,
I need help with another excel calculation here. I have attached a sample data to this string. I need to get the average resp and resolution time for the tasks for respective priorities and the average should not include "NA" value in that. Can you please help me?

Thanks,
Vishal
 

Answer:Solved: Excel formula help

6 more replies
Relevance 62.32%

Hi all,

Please tell the formula to calculate days in excel.

i need to subtract lots of dates like this, please give me the formula.

Eg: 01.02.2012 -12.02.2012=12 days

Thanks
N.Prem Kumar
 

Answer:Solved: Excel formula

if you do a replace and change the . to a /
then format the cells to date and
then you can just take one from the other

But that would calculate as 11 days

if you need to still see in the dd.mm.yyyy for format
do a custom format and set to

DD.MM.YYYY
 

1 more replies
Relevance 62.32%

I have inherited an Excel 2007 macro workbook and am having trouble with a formula: =TEXT(ROUND(D9,2),"0.00")& " " & IF($C9<D9,"&#9650;", IF($C9=D9,"&#9679;","&#9660;")).

The increases and decreases are working fine, but the values that remain the same ($C9=D9,"&#9679;",) are displaying as an increase or decrease instead of a neutral. How do I fix this?
 

Answer:Solved: Need help with Excel formula

I figured it out. Even though my numbers displayed as x.xx, the actual number was x.xxxxxxxxxxx. As soon as I truncated, the formula worked.
 

1 more replies
Relevance 62.32%

Guys I would appreciate any help if possible with a excel formula. Here is the scenario I have two groups of data on the same spreadsheet 4 columns total. I need to compare column B to column D and if column B is equal to a value in column D then I need it to compare the corresponding record in A with C and if they match I need a simple Yes or No. See below if this is confusing:

Column A Column B Column C Column D Ans
testerjoe 1234567 testerjoe 9876543 NO
testersam 5555555 testersam 5555555 YES
Any help would be great I can get it to tell me if column A is in C but I can't figure out the rest...Thanks in advance.
 

Answer:Solved: Excel Formula Help

16 more replies
Relevance 62.32%

I have a couple of formulas that are not working the way I would like them to. Any assistance would be appreciated.

1. The first is simple, but for some reason is working.

='Associate 1'!D3

All I am trying to do is copy the contents of the cell from one worksheet to another to eliminate duplicate data entry. I have done it many times, but for some reason it is only showing the formula and not the cell contents.

2. The second is showing #DIV/0! until data is present and I want it to remain null until data is present. I have tried testing the data in an IF statement and returning null if no data is present, but I cannot get rid of the error message.

=SUM(T145:T147)-T146

Again, any help is appreciated and if you need more information don't hesitate to ask.
 

Answer:Solved: Excel Formula Help

6 more replies
Relevance 62.32%

The following code is behind my Excel spreadsheet.
Code:

Private Sub Workbook_Open()

Dim myCount
Dim i

Application.ScreenUpdating = False

For Each vCell In Range("D2:I2")
If IsEmpty(vCell) Then
vCell.Select
Exit Sub
End If
Next
Range("K4").Select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="time"

ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub

How can get the code to check "D9:I9" as well?
 

Answer:Solved: Excel Formula

16 more replies
Relevance 62.32%

Hello

I have a simple formula =((B5+C5)*0.034)+0.2 and all it does is calculate my paypal fees. However I do not always use paypal so I was hoping this could be incorporated something like this

if paypal is used calculate =((B5+C5)*0.034)+0.2 if no payment is made do nothing

Where b5 is item cost and c5 is postage cost,you see at the moment I have two spreadsheets ones for the postage and one for non postage and its a bit tedious to fill out.

Thanks
 

Answer:Solved: Excel Formula Help

just use IF
You need something to tell if its a paypal transaction

so
IF ( test for paypal, ((B5+C5)*0.034)+0.2 , 0)

how do you know if paypal
 

3 more replies
Relevance 62.32%

Is there a way to input a variable (wild card) that refers to the current row (x) in this formula ??

=SUM(B1:Bx)-SUM(A1:Ax)

Or do I have to specify the current row number in each row ??
I rather just Copy n Paste a generic formula for each row.

Hope I said that right .. Just a rookie toying with Excel.
 

Answer:Solved: Excel Formula help

16 more replies
Relevance 62.32%

Hi,

Please see attached Excel Doc,

I have set up a workday function to calculate the date a report is due.

If the report is late there is a column to enter the date it was chased.

I need the table to recognise when a date is entered in the DUE DATE column and
begin showing the Workday calulation from this column instead.

Can this be done? am I just stupid...

any help would be greatly appreciated.

Thank You
 

Answer:Solved: EXCEL FORMULA - Can you help? PLEASE!

Hi, welcome to the forum,

I editted your formula to include the chaser date. If this date is filled then the conditional format will be triggered by this.

Is this what you wanted?

You'll have to copy the formula down the rest of the list
 

3 more replies
Relevance 62.32%

My goal is to have one master worksheet and whenever another worksheet is updated, the master is updated. Basically just copying the number from the other worksheet into a cell on the master. It's confusing the heck out of me.

I want the number from sheet 2 cell B3 to be reproduced on Sheet 1 cell C4.
 

Answer:Solved: Excel Formula Help!! please!

6 more replies
Relevance 62.32%

Can someone give me the formula to solve the following problem.

I need 5% of a number until the total reaches 5000 then I need 2.5% of everything over that amount.

Example:

150000 should equal 6,250

The first 100000*5% would equal 5000
everything over 5000 is taxed at 2.5% so
50,000 * 2.5% = 1,250

Thanks for your help.
 

Answer:Solved: Need a formula for Excel

6 more replies
Relevance 62.32%

I need a formula to expand on what I already have. I am using this to track insurance policy expiration dates. As it is right now, I have a column with the expiration date of the policy, and if it is expired it says "expired" in the column next to it. Since for one person I have several policies, in column b it notes "expired" if any of the policies for that person are expired.

My problem is that this only tells me the policy is expired after the expiration happens. What I want to do is modify the formula so that it gives me a warning of a policy that is about to expire. For instance, it the expiration date is less than 30 days away, than the cell next to the policy expiration date will say "pending exp", and if the policy is expired, it still will say "expired"

Thanks for any help. Sample is attached.
 

Answer:Solved: Help with Excel Formula

6 more replies
Relevance 62.32%

What would be the correct way to make the following formula check for 2 values:

=IF(A5="","",A5)

I want cell to be null if A5 is null or if it contains the text "Grand Total".
 

Answer:Solved: Excel Formula Help

These are known as "Nested" IF Statements

=IF((OR(A5="",A5="Grand Total")),"",A5)

Rollin
 

2 more replies
Relevance 62.32%

I'm trying to find a formula for cell 4 which uses cells 1-3. I will give an example of what cells 1-3 will be and then what I need 4 to be.

Cell 1=35
Cell 2=44
Cell 3=5

So now I need Cell 4 to equal 4. The formula I need is similar to C1+/-5 (so 30-40) and then C2-40. Or if Cell 2 was 26 then I would need it to be the 30-C2. In both cases I need Cell 4 to say it equals 4 when Cell 2 is both 26 and 44. At the same time though cell 3 won't be fixed so if cell 3 was equal to 6 then cell 4 would equal 3 when cell 2 is 26 or 44. I know this is kind of confusing, I just don't know how to explain it.
 

Answer:Solved: Excel Formula help

11 more replies
Relevance 62.32%

I'm looking for a formula that will calculate the number of years between two dates. Specifically, I have a list of employee and their start dates. I am looking for a formula that will give me the number of years that they have been with the company on their next anniverary date.

I am at work so I am using Excel 2002.

Appreciate any assistance.
 

Answer:[SOLVED] Excel Formula help

7 more replies
Relevance 62.32%

I am normally ok with formulas but for some reason my brain just cannot grasp this. I know it is simple but I need some help.

I have a line that has a unit price, a discount amount, a corporate price (that auto-populates the discounted price) and then of course the last line is the line total that auto-populates depending on it which cost needs to be reflected.

Now the problem is in the corporate price cell- if there is no discount, it shows the actual unit price and I need it to be blank.

I have attached an example for review.

Thanks for any help provided, it is much appreciated.
 

Answer:Solved: Excel formula help

Might this help?
=IF(E22>0,(D22-(D22*E22)),"")
[If there is a Discount price, do this calculation, If there is no Discount Price, put "" in F22] (As opposed to F22 being 'FALSE')
 

2 more replies
Relevance 62.32%

I need an excel formula that calculates rental rates at $82 per day for the first 90, $61.50 per day for next 90 days, and $45.10 per day thereafter. I have an IF statement that will calculate the first 2 rates. I just don't know how to add the third.

Any help would be greatly appreciated.
 

Answer:Solved: Excel Formula

8 more replies
Relevance 62.32%

Hello,

I'm looking for a little help with an Excel formula. It is for a study plan I am creating. I think it should be an SUMIF something, I just can't quite figure it out.

I have my list of subjects in column C, a few other columns, a notional study time in F and my actual study time in G.

Further down I am doing a daily update on which subject I spent my time on in column C (using a drop down list) and the hours I spent on it in D.

So the formula I'm trying to create is for my actual study time, in column G. What I want it to do is, if any of my range of data from the daily updates equals the Subject then it should display the total number of hours I spent on it, which have been entered into column D, and this should carry on down my spreadsheet for all 8 subjects.

I hope this makes sense and is possible! Any help would be much appreciated

Penny x
 

Answer:Solved: Excel Formula help

7 more replies
Relevance 62.32%

I would like to write a formula for the following information:

If order 5-10 cases then 10% discount and if you order 11 and over cases, then 15% discount.
 

Answer:Solved: Excel Formula

9 more replies
Relevance 62.32%

I would like to create a formula that creates sequential numbers for rows that match the criteria of having "apple" in column A AND, 99324 to 99328 OR 99334 to 99337 in column C.

I want to following:
Column A Column C Column Q
apple 99324 1
apple 99327 2
apple 99334 3
apple 99335 4
apple 99337 5
I tried using the following formula and it did not work:
=IF(AND(A7="apple",C7>=99324,C7<=99328,if(or(A7="apple",C7>=99334,C7<=99337),MAX($Q$2:$Q6)+1," ")))
Please help!!!
 

Answer:Solved: Excel Formula If AND/OR

i made a mistake in the formula, and have corrected it. nevertheless, my point is still the same. the formula does not work. when i use it i get an error message
 

3 more replies
Relevance 62.32%

I have the formula below:
Code:

=If(abs(A1-A2)<=0.01, "Yes", "No")
[code]

The difference comes out to be -0.01, but using the ABS funtion the number is converted to 0.01. However, the formula is still producing a "No" in the cell.

What am I doing wrong? Why is the formula not recognizing that the values are equal?

 

Answer:Solved: Excel Formula

11 more replies
Relevance 62.32%

In the attached .xls I have 5 tabs in total – 4 (A,B,C,D) with columns indicating Budget, Actual, etc., plus 1 Summary Worksheet.

I want to transfer the numbers (under ACT column “E” only in the A B C D tabs) to the ‘summary’ sheet without having to do it the long-way. Not all tabs have the same acc numbers - they have some in common - but not all; however, all acc numbers are on the summary sheet.

I thought a VLOOKUP – but I’ve only ever done one before a few years back – and I'm not sure if it would work in this case.

Much appreciated!
 

Answer:Solved: Excel - Not sure which formula to use?

Try this
 

3 more replies
Relevance 62.32%

if i have a formula in a cell ex:=IF(H7>0U9,"") the ,"" makes the cell empty untill something gets put into H7. Now the problem when i try to =sum the numbers in that column it gives me a error. I want to be able to add the cells with no numbers but has =IF(H7>0U9,"") in it, and if there is a number in there the =sum works just fine. Its only when the cells with that formula is left blank that it gives me the error. Any idea's? Thanks in advance.
 

Answer:Solved: Excel formula help

16 more replies
Relevance 62.32%

HI,

Quick formula question:

If F3 is not empty, It shows FALSE, instead of blank. How come?

Thank you

=IF(AND(OR(Data!B3=1,Data!B3=2,Data!B3=3),Data!D3="P"),
IF(Data!IF3="",IF(ISERROR(SEARCH("Car",Data!CL5,1)),"","X")),"")
 

Answer:Solved: Excel formula

you have a few embedded IFs there so trying to debug them

is that forumla correct - seem to be missing the last FALSE statement for the first IF
also there is NO F3 in the formula

=IF(AND(OR(Data!B3=1,Data!B3=2,Data!B3=3),Data!D3="P"),
IF(Data!IF3="",IF(ISERROR(SEARCH("Car",Data!CL5,1)),"","X")),"")Click to expand...


Data!IF3Click to expand...

If i have split this correctly and coded in colour correctly - so
1st IF = Red - Has Test, TRUE , but no false
2nd IF = Green - has Test, TRUE, False
3rd IF = Blue - you can see that has the Test, True, False

= IF(

Test1: AND(OR(Data!B3=1,Data!B3=2,Data!B3=3),Data!D3="P"),

TRUE1: IF ( Data!IF3="", IF (ISERROR(SEARCH("Car",Data!CL5,1)),"","X") ,"")

, False1: ???
 

1 more replies
Relevance 61.91%

I am trying to create a worksheet that will pull information from other worksheets in the same workbook. There are about 100 worksheets and Column 1 has the worksheet number, column 2 lists the item number from the specified sheet. I want a formula that says if A1 is equal to "Sheet***" the search that sheet for item # listed in B1 and return the value of C1.

I can do a lookup but I can not find how to have it look at each sheet and not just a certain one that I chose.

Help please
 

Answer:Solved: Excel 2007 Formula HELP!!!

9 more replies
Relevance 61.91%

Hi, I'm wondering if the following is a possibility?

Essentially what I need to do is have a formula in cell AY that says

IF A2="Salary" then use the formula currently in AY which is: =(IF(AV60<>"",AV60,AO60)) , if A2 isn't salary than use the value in AX

My question is, is it somehow possible to perform a function in AY only if A2 = Salary?
Thanks for your help guys!
 

Answer:Solved: Excel - IF function with formula rather than value?

6 more replies
Relevance 61.91%

I’ll try to explain as best I can exactly what I am looking for excel to do:
I download a report each month into excel. I need to take the figures in one column of this report and put them into a separate fixed excel worksheet. When I say fixed, I mean that none of the info can change except the figures from the downloaded report. The way I do it just now is manually match the codes in each sheet and type in the appropriate figure for that code.
My question is, can excel do this for me quicker? I have tried vlookup and index-match, but always end up with some figures being correct, which is nice, some #N/A and some #REF! Now I may have made some errors in the formula used, but it is driving me nuts.

Thanks in advance.
 

Answer:Solved: Excel formula help needed

9 more replies
Relevance 61.91%

I am a teacher and I have a spreadsheet I am working on that is to compare 2 test results and see what the difference is between the 2 tests (+ or -)

Example: We have winter test results in Column A then we have fall test results in column B. I want column C to show me the loss or gains between the 2 tests
example:
A (winter result) B (fall result) C = + or -
Student 1: 175 145 +30 points gained since the fall test results

But the problem I am running into is that if I have a student who did not test in the Fall and I have a 0 in that column it is giving me negative winter test result (-175) I want it to give me a 0 + or - result. Also vice versa if I have a 0 in the winter (A) column but a 145 in the fall (B) column.

If either column A or B is a 0 I want column C to have a 0 + or - result.

I sure hope I haven't talked in total circles and you can make heads or tails up above.

Thanks in advance.
 

Answer:Solved: Excel Formula Needed (If possible)

If you open the attached Excel File and look at the formulas in Column C , I think thats what you want.
 

3 more replies
Relevance 61.91%

Hi there,

Could anyone help me with a formula or macro that will read the data in column A and then display a value in column C in the attached .xls?

EZ-Links would display 5
Hutton would display 3

Many thanks!
 

Answer:Solved: Excel Formula or Macro help please

Place the following formula in C11 and copy down for all rows. Will there ever be any other values that you need to check for?

=IF(A11="EZ-Links",5,IF(A11="Hutton",3,""))

Regards,
Rollin
 

3 more replies
Relevance 61.91%

I need a formula to provide a count. I have 6 prices, 3 of the prices are the same, 1 is greater than the mode price and 2 are less than the mode. I need to be able to have these counts automatically generated through a formula. I used the count function for the total price count, the mode function for the most constant price, but I can't figure out a formula to give me a count for the prices that are greater or less than the mode (constant price). I have tried using countif, but it comes back as zero. Any ideas out there????
 

Answer:Solved: Excel Formula Needed

16 more replies