Home

Formulas and Functions

905 Conversations

Latest Activity

Custom List Message Item

Worksheets("REPORTS").Range("S22").Formula = "=+'18 hole scores'!RC" & DateColumn

.

I have had this formula for 4 years and it worked fine. It puts a vaue like C27 into cell S22.

I have not made any changes to the macro.

Now, the result in S22 is literally RC

... Read More
107 Views
2 Replies

Hi George,

 

I guess it is related with R1C1 reference style.

Follow below path in your excel window

Excel Options -> Formulas -> Working with Formulas -> R1C1 reference styl

... Read More

Hi George,  are you able to attach your file or some screenshots?

 

Thanks

Hello All

 

I have a quick question.

 

I want to categorize my data in three categories like Good Average and Bad, the whole sheet consists of different groups. Below is the example of data, the file is attached as well.

 

S/NGroupsAmountCategory Scale for Grou
... Read More
197 Views
4 Replies

Hello Muhammad

 

Restrcuture your scale tables into one table.

 

Group - Lower - Category

A - 0 - Bad

A - 12 - Good

A - 16 - Average

A - 21 - Bad

etc.

 

In my example the new table

... Read More
Best Response confirmed by Muhammad Khan (New Contributor)

I have a spreadsheet with 3 tabs, "Scorecard" used to pull in data from the other two tabs "Raw Data Tod" and "Raw Data Jim".

 

I have two formuals as per below that work independently of each other, however I want to combine the two formulas into one, so i

... Read More
45 Views
0 Reply

I need to combine a bit of text, followed by a formula in a single cell.  For example, I  have a multi-worksheet spreadsheet that uses data from page 1 to fill in the same data on pages 2 & 3; part numbers, customer name and the like.

I want a single cell

... Read More
156 Views
2 Replies

Hi @chrisp

 

="Revision "&'SETUP-1'!R4

Read More

Hi all,

 

I am trying to get rid of some legacy formulas in one of my Excel table. When trying to remove the content in the table or pasting everything as values, I can still find the XML tag <calculatedColumnFormula> with the old formula in the XML for som

... Read More
182 Views
2 Replies

Hi Edouard,

 

When you create a formula in a Table then it gets stored in that column.  I like to think of it as being "stored" in the column header.

 

That way even when you

... Read More

Hi Edouard,

 

Interesting question. IMHO, as soon as calculatedColumnFormula tag appeared in Table.xml you can't remove it within UI.

 

Tag appears if you enter the formula i

... Read More

Hi,

 

I am trying to use the below formula to analyze some data however the formula seems to be stopping at "Medium" and going no further.  I have also tried to use the "IFS" function but that is just returning "Name?" error message.  Does someone know why

... Read More
114 Views
2 Replies
Worked like a charm! Thanks! I do not use Lookup formulas every day so very rusty!

Hello Claire

 

A simple LOOKUP()-formula will do:

=LOOKUP(G2,{0;1;5;10;15},{"";"Low";"Medium";"High";"Very High"})

 

Hi, I'm sure excel can do this I just can't figure out how.

 

I have a spreadsheet which has department names in the first column and dates across the top row. I have shaded the cell relevant to when each unit is scheduled to start a piece of work. How do I

... Read More
107 Views
1 Reply

Hi @Jessica Evans

 

There's no standard formula you can use to count shaded cells

 

The easiest solution would be to type a 1 in each cell you that corresponds to the start d

... Read More

I am trying to create a formula and cant seem to figure this one out.  a value is entered in column A and i need a formula in column B that if A<=25 then .5 will appear in the cell. if A>=26 but =< 50 then .35 will appear in the cell. if A >= 51 and <=65

... Read More
81 Views
2 Replies

Hi Jim,

 

That's classical nested IF with possible alternatives, very good explanations and examples are here https://exceljet.net/nested-ifs

 

As one of alternative could be

...
										Read More

								

Hello,
I would like to know which formula I can use in days calculation between two dates (Counting the first date).
For example:
I have a course starting on 1/4/2017 and ending on 5/4/2017 so the direct calculation shows the different is 4 days, but the rea

... Read More
80 Views
1 Reply

Hi Ahmed

 

If your have date values stored in your cells then, End_Date - Start_Date +1 gives you the duration in days.  As far as i recall, there are know special functi

... Read More

Hi I'm trying to set up a children attendance and payment work sheet.  I need help as to how to set up a formular to calculate payment depending on number of children attending.  So basically if cell E2 is 0 = 0, if cell E2 is 1 = 4, if cell E2 is 2= 6, i

... Read More
120 Views
2 Replies

Hi Lin

 

I like @Haytham Amairah's solution.

 

Here's my take using a vlookup or safer still an INDEX MATCH

 

There's no benefit over Haytham's solution in your particular scen

... Read More

Hi Lin,

 

Simply, you have to use somthing like this:

 

=IF(NOT(ISNUMBER(E2)),"",
IF(E2=0,0,
IF(E2=1,4,
IF(E2=2,6,
IF(E2>=3,8,
)))))
Read More
Best Response confirmed by Lin Wang (Occasional Visitor)

I am trying to update an Excel document that was, I believe, retrieved at some point from an online resource. I have two lines tracking certain metrics, and need to add two more. However, when I type in the formulas I need for the additional metric tracki

... Read More
130 Views
3 Replies

Hi Amy

 

The formula has been created as an array formula  that means you need to press Ctrl+Shift+Enter after making changes rather than the normal pressing of enter.

 

It's

... Read More

I need to know the way to make the selection when i move from a cell to another be slow ( like SAP navigator ) i used to it in my old PC but when i get the new it disappeared

43 Views
0 Reply

I'm trying to average (=average(f7:f40) the differance between two values. This is the formula (=c6-c7, =c7-c8, and so forth) I use to calculate the differance. Sometimes the value is a negative number witch throws the average off for the differance. I ne

... Read More
76 Views
1 Reply

Hello Calvin

 

=ABS(C6-C7)

=MAX(C6:C7)-MIN(C6:C7)

I have the following formula and am getting the #Value error ...  =IF(OR(E34="--",E34=0,E32="--",E32=0),"--",ROUND(E32/-E34,2)&":1")  when there are values in appropriate Rows it calulates correctly, when no data is present I get the #Vakue error.

Read More
140 Views
3 Replies

Hi Jim

It works for me unless I press space bar to clear the number, in which case I get the #Value error

 

You could do this

 

=IF(OR(E34="--",N(E34)=0,E32="--",N(E32)=0),"--

... Read More
Best Response confirmed by Jim Jenkins (New Contributor)

I have a datatable that is added to daily by date.  I am using the formula

=COUNTIFS(FirstVisit,"Yes",Stake,A6)

to count number of "first time visits" to our facility for each group ("Stake"). 

However, I want to write the above formula to only count the dat

... Read More
131 Views
2 Replies

Hi Stephen,

 

That could be something like this

=COUNTIFS(FirstVisit,"Yes",Stake,A6,VisitDate,">" & EOMONTH(DATE(2017,2,1),-1),VisitDate,"<=" & EOMONTH(DATE(2017,2,1),0))

whe

... Read More
Best Response confirmed by Stephen Meyers (New Contributor)

I've forgoten how to how to write a simple formula that will keep the cell blank if there isn't any data in an adjacent otherwise it should do the calculation.

 

This is how I wrote it.

=IF((E7=" "," "),(F6+E7))

 

Thank you 

Read More
158 Views
4 Replies

Dear Chris,

You can also replace this part: E7="" , by this one: ISBLANK(E7)
This will help you to restrict the applying of the formula more then ever.

The applying with thi

... Read More

Hello Chris

 

=IF(E7="","",F6+E7)

Help, I need to create an organizational chart.  I downloaded a template from Microsoft templates called Org charts (visual) (it has 4 different org charts.  2 using smart art and 2 only using cells & formatting see the instructions below:  

 

Use to show h

... Read More
59 Views
0 Reply

well. I need a formula to find out several passive object.
the qustion is that I have three person with three diffrent sallary who should work in passive time for me. but i know The total time that person should work for me is 450 hours. and The contract c

... Read More
89 Views
1 Reply

Hi Kyvan,

 

Are you sure your figures are correct? Even if we put all 450 Hrs on less paid C person (A=B=0) totally it'll be 450*750,000 = 337,500,000 - much more than your

... Read More

I have bookmarked certain text in a Word document, and I'm trying to link that text into an excel spreadsheet using Excel 2016. 

 

Previously w Office 2010 I was able to do this using this formula:

 

=Word.Document.12|'C:\Users\wlawrence\Desktop\Data\Source.d

... Read More
61 Views
1 Reply

Hi William,

 

I tested right now, it works like this

=Word.Document|'G:\Data\TB.docx'!'!Test1'

Excel 2016 and Word2016

 

Read More

i created a workbook using excel 2016. when using it on 2007 the vlookup functions do not automatically fill after filling in the drop down choices. i have to save the sheet then reopoen it for everything to populate it. all calculations are set for autom

... Read More
155 Views
1 Reply

Hi Danny

 

Can you clarify what you mean by hte vlookup functions doesn't automatically fill

 

Thanks

In Excel 2013: Im trying to count the names in column A (not including TBA) =COUNTIF A2:A40, "<TBA")

plus the number of Fitters in column B =COUNTIF A2:A40, "<TBA", B2:B40,"=Fitter",

plus year in column E, (only if greater than 2017) however I keep getting

... Read More
174 Views
4 Replies

Dear,

 

Try this formula:

=SUM(COUNTIF(A2:C40,{">12/31/2017","<>TBA","Fitter"}))

But you have to move the date column from column E to C, and select all the three columns 
... Read More

Can you please help me creat a equal equation that will look to see if the numnber in colunm A is the same in column B

 

93 Views
2 Replies

Hi Jennifer,

You can place this formula in cell C1 =IF(A1=B1,"Same","Not Same").

 

It will return "Same" if the numbers or values in Columns A and B are same, and return "No

... Read More

Hello Jennifr

 

=A1=B1

I am trying to link entered start and end dates into a calendar timeline... i am able to get it to work for one set of start/end dates with the originally planned and the actual dates...but then i cannot add a second plot of the original/planned dates....

... Read More
67 Views
3 Replies

=If(AND(IF(AND(DY$5>=$X8,DY$5<=$Y8),$W$6,if(and(DY$5>=$AA8,DY$5<=$AB8),"")),if(and(DY$5>=$H8,DY$5<=$I8),$G$6,If(and(DY$5>=$K8,DY$5<=$L8),""))))

 

*This is the function, sor

... Read More

Hey guys, I'm having some trouble writing an equation for a homework assignment. I'm supposed to Find the average of a row (E6-P6) and then round that to the nearest $10. I keep getting pop-ups that my formua is wrong, and I have no idea where to go from

... Read More
109 Views
4 Replies

Try below formula:-

 

=MROUND(AVERAGE(E6:P6),10)

 

 

Regards,

DILIPandey

When using VLOOKUP, how can I get it to return a completely empty cell (with no spaces) when there is no result vs the zero length text cell that ("") produces when used?

114 Views
2 Replies
Hi Cliff, what is the next step if you get completely empty cell? what is your final objective? Regards, DILIPandey

Hello Cliff

 

You want something like this:

Give us a proper NULL() worksheet function.

NULL Worksheet Function

 

Go there, sign in and vote for this issue.

 

Read More