Home Excel

Formulas and Functions

159 Conversations

Latest Activity

Custom List Message Item

Hi. I'm trying to create a point based fitness log system where with each workout you earn points and with the points earned you level up over time. 


My issue is what would be the best way to do this besides using the "IF" function?



Read More
3 Replies

To get a level from a list of steps, first create a little table that looks similar to this:




(lets assume this is on Sheet2

... Read More



I am tracking the number of falls for about 100 people, but need to know how many falls each have had in the last 6 months. I would like for this number to automatically update with the current date.


I have the months set across C1:N1 and the people

... Read More
3 Replies

Send that excel file.


Or give an example.

In cell B5 it currently says 3 Foot.  In different scenarios it can also say 6 Foot or Even.  I need cell C7 to reference the appropriate column on tab 1C in regards to Even, 3 Foot, or 6 Foot, then I need it to reference the corresponding number in cell

... Read More
13 Replies

Hi John


It's better to provide a workbook than a bunch of photos.


This is probably easier than I think but at 4:30 in the morning not thinking clearly.


Test#1 = ID = "Corn" and "Tomato"
Test #2 = ID = (starts with "P")
Both test conditions should return a "T" or "F" result (Boolean is OK)
... Read More
10 Replies
Does not work, remember, the ID values occur multiple times with different conditions and I want to identify ID's only where both conditions are true, not ID's where eith... Read More





Below is the function I'm using to track the overall weight loss percentage of members over a 12 week period. The problem is that, say the result for (D3-E3)/D3 is .045, or 4.5%, the result of the ifs function shows 1.045, or 104.5%. Please advise on how

... Read More
1 Reply

Hi Chelsea,


In your expression you have sum of two functions, IF and IFS. IFS itself shall give correct result, i guess +1 is added by IF. Your re-written expression look

... Read More

I would like to create two drop-down lists. One drop-down list would contain the type of Dessert (see below - Ice Cream, Cookie, Jello. In the second drop-down list, I would like the user to only be permitted to select the flavor associated with the desse

... Read More
1 Reply



The solution is as follows:


In the first dropdown list:


Dropdown List.JPG



In the second dropdown list, put this formula in the data validation source:

=IF(D2="Ice Cream",B2:B4,IF(D2="
... Read More

I have a Table (obviously), one column (B) is labeled "Status" and is limited to a data validation list; the next column to be considered in this question is column (D) which is labeled "Date Used" into which is to be entered a date.


The problem I am expe

... Read More
6 Replies
FORMULA in cell B2
=IF(D2<>"","USED","UNUSED"). Although CF will work it doesn't necessarily means the user will necessarily select the correct option. Automating the colu... Read More

Hello Russ


You can set up a data validation in column D so that a date can only be entered the text in column B is not equal to "Unused".


Or you can set up a conditional

... Read More

Hi, I am trying to create a formula for my share trading spreadsheet (I just added a comment in here but I think I deleted it somehow :-(

If I trade 1 contract the commission paid is $4.25, if I trade more than 1 contract the commission is $0.85 per additi

... Read More
2 Replies

HI Anthony


Lets assume the number of trades is in cell A1 then your formula is


=IF(A1=0,0,  4.25+(A1-1)*0.85)


so IF 1 contract is sold it will be $4.25 + (1-1) *0.85

so IF

... Read More

Hi, I'm trying to create a formula for my share trading spreadsheet. It is to calculate the commission paid per contract traded.

If there is only 1 contract the commission is $4.25, if more than 1 contract it is an extra $0.85 per contract

What I'd like to

... Read More
1 Reply



I'm having a little struggle with something that I think it can't be so hard.


I have two blocks of 6 columns each. What I want to do is to make a rule for the first block.

I want to color the first block depending on different conditions.

If the divi

... Read More
5 Replies



I'm not quite sure I understand your requirement completely. For the first column, fine. But the second column, should that compare the first column of the blocks,

... Read More
Best Response

I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell.

This formula seems to work for two variables but I can't add any more variables too it.


... Read More
12 Replies

Hi Reuben Helder,


Good day,


Please refer below formula to add more variable.

If statment is false then I have considered criteria is "NO MATCH"


... Read More

Dear Reuben Helder,

You may also use the below formula for the task. I have retained the same cell references that have been used by Detlef Lewin in the solution that he h

... Read More

Ah, a classic one.


Key words in column F, returned text in column G, text to check in A1.




Read More



I am trying to referance a cell within a column to another cell. The column contains a list that I need to chop and change and often delete individual cells with it. If I delete a cell the problem is I get a REF! error in the cell I want the text to a

... Read More
5 Replies

Hi Aoife,


you might be able to use lookup functions, such as VLOOKUP or INDEX/MATCH in case you have a reference column for the purposes of lookup. Are you able to post a

... Read More

Dear community,

I'm struggling with setting up a formula within conditional formatting. I tried setting up a new rule but to no avail.

The task is to set up a rule that highlights the data in column b3;b10 whenever the quantity is 20% lower than the calcula

... Read More
5 Replies

Hi Daan,


For conditional formatting use the rule


and apply it to



I have a rather ragged data set, which is split over three sets of columns. In columns A, H and O I have multiple instances of the same numbers representing sample codes. In the other columns I have individual attributes which have been scored out of 100.

... Read More
2 Replies

Hi Terri,


in line with the approach referred to by Jon, your formulas would be as follows:







... Read More

You could try an array formula using MEDIAN(IF(),IF(),IF()) and STDEV(IF(),IF(),IF()) as explained here:



... Read More

I've recently seen a few posts on various sites with people requiring assistance in devloping dependent drop downs.   

  • For example drop down box A  contains Tea, Coffe, Juice 
  • If you select Coffee then drop down box B should show Flat White, Cappuccino etc 

... Read More
3 Replies

I have a budget tracker.  once i record an expense, I have  drop down list for the categories they fall into.  however, once I select the drop down menu, I want the amoun

... Read More
I have created several tutorials on this topic which can be found here - http://www.contextures.com/excelfilesRoger.html For those with XL2007 upward, I would recommend t... Read More