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?

 

Thanks.

Read More
43 Views
3 Replies

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

 

0Beginner
50Intermediate
100Pro
200Expert
1000Guru

 

(lets assume this is on Sheet2

... Read More

Hey,

 

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
71 Views
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
110 Views
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)
  
ConditionID
Apple000001
Berry00000
... Read More
332 Views
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

Hello

 

=OR(A2="Corn",A2="Tomato")
=LEFT(A2)="P"

 

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
34 Views
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
50 Views
1 Reply

Hi,

 

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
86 Views
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
61 Views
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
41 Views
1 Reply

Hello,

 

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
135 Views
5 Replies

Hello,

 

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.

=IFERROR(IF(SEARCH("*Sales*",B3,1),"Sales"),IF(SEARCH("*Arch*",B3,1)

... Read More
173 Views
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"

IF(B3="MECH","MECHANICAL",IF

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

 

=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6)

 

Read More

Hi,

 

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
84 Views
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
92 Views
5 Replies

Hi Daan,

 

For conditional formatting use the rule

=($C3<=0.8*$C$11)

and apply it to

$B$3:$B$10

 

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
37 Views
2 Replies

Hi Terri,

 

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

 

Median:

=MEDIAN(IF($M$2:$M$76=$M$78,$N$2:$N$76,""))

 

STDEV:

=STDEV(IF($M$2:$M$76=$

... Read More

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

 

http://stackoverflow.com/questions/6752724/help-needed-with-median-if-in-excel

... 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
154 Views
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