Home Excel

Formulas and Functions

64 Conversations

Latest Activity

Custom List Message Item

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
29 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
53 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

 

34 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
32 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
56 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?

38 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

Good morning!

 

I have an itemized list of monthly meetings and deliverables on one worksheet which work day asignments and whose dates change based on the month chosen. There are several meetings and deliverables on a given. How can I link to a one page ca

... Read More
27 Views
1 Reply

So you need vlookup to return multiple matches..  Yes... it possible..can you share a data sample with your expected output ?

 

 

Regards,

DILIPandey

Read More

Please calculate the following formular without any division in it:

 

=int((6.05-6)*100)

 

 

89 Views
8 Replies

I am new at this and am not sure how to word my question.

 

In my workbook titled "Menu Analysis" I have 16 colums.  I need to write a formula that will populate the 13th column with one of four terms if they meet the following criteria:

if D2 is low and L2

... Read More
79 Views
3 Replies

Monica, attached is Menu Engineering sample file, in Example tab you'll find the formula

Best Response

I have Excel 2016 on Windows 10. The Change Case formula doesn't seem to work. In cell D3 I type =PROPER(C3), and cell D3 treats that like a text entry rather than importing data from C3. It's probably me, but I can't see what I'm doing incorrectly.

Read More
54 Views
2 Replies
Thanks!!!

Hello Ken

 

Either you are in view-formula-mode or the cell is text-formatted.

 

Solution 1

CTRL-SHIFT-´

 

Solution 2

Change the number format to "General". Then press F2 and ENT

... Read More

Hi,

 

I need to cross reference values in 2 different cells, and depending on if one is higher or lower than the other populate a different cell with a colour. What I also want to do is depending on the difference between the numbers give it a colour.

 

i.e.

i

... Read More
34 Views
1 Reply

Hi Mark,

 

Sure. Apply Conditional Formatting (shall be in Ribbon menu) to cell F based on formula, five rules practically same way as you formulated them here. e.g. first

... Read More

Hi, 

 

I have an item list as string in one column separated by "|" and I have a mapping table of the item list. What I need to do is compare the string with the mapping table and return all the items that are NOT in the string.

 

Column X in Excel:

AAA|BBB|DD

... Read More
61 Views
5 Replies
A VBA UDF also works:
Option Explicit

Public Function ShowMissingItems(TheText As String, MappingTable) As String
Dim vMappingTable As Variant
Dim lRow As Long

If TypeName(Map... Read More

Hi Raj,

 

The easiest way is to use Power Query (Get&Transform in Excel 2016) as attached. If you are not familiar with it using formulas will be more complicated.

 

Read More

I have a spreadsheet which shows the statuses in a partifular course (completed, not started, in progress) for a division which includes fifteen regions and over 1500 associates .  I need to find the number of associates in each region who are in a partic

... Read More
84 Views
7 Replies

Melissa, if you give a sample of how your data is structured (up to 10 rows) it'll be much easier to answer.

Older versions, when I typed in a function into a cell, and wanted to know more about it, I would scroll over the function name and it would highlight and if I clicked on it, it took me to that particular function reference as a window on the same page.  

... Read More
30 Views
0 Reply

Hello,

I am trying to run descriptive stats on aggregate pre and posttest survey data.  I highlighted the pretest data, and selected descriptive statistics.  However, instead of providing descriptive stats on the entire survey, the results only give descri

... Read More
50 Views
3 Replies

Okay, I think I figured out how to do this.

 

I obtained the average for each survey item by highlighting item data, and selecting average.  The calculation appears in the

... Read More