Home

Formulas and Functions

892 Conversations

Latest Activity

Custom List Message Item

=IF(E2=1, "letter 2", IF(E2=2,"letter 3", IF(E2=3, "call", "nothing issued")))

The above gives me the correct output necessary for determining which letter I next need to issue, however, I would like to be able to limit the output by cell E3 which contain

... Read More
39 Views
1 Reply

One more IF on the top

=IF(E3=TODAY()-7,IF(E2=1, "letter 2", IF(E2=2,"letter 3", IF(E2=3, "call", "nothing issued"))),"")

 

I need to calculate accrued interest with an end date of 10.27.2017 rather than "TODAY" How do I replace the date in this formula?

=FV(0.1/365,DAYS(TODAY(),B5),0,-D5)

 

Any help would be appreciated! Thank you!

34 Views
1 Reply

Hello,

 

put the desired date into a cell, for example A1, then replace Today() with A1

 

=FV(0.1/365,DAYS(A1,B5),0,-D5)

 

This way you can quickly change the date withou

... Read More
Best Response confirmed by Morgan McCloskey (Occasional Visitor)
Hello.  I'm having trouble with the syntax for the argument to say "does not equal text in cell M12" with the portion of my formula below.
 
IF(AND(C15=C16,COUNTIF(U$7:U$30,O16),COUNTIF(U$7:U$30,"<>M12"))
 
I've included the entire string below and the sh
... Read More
49 Views
2 Replies

Sean,

 

try this:

IF(AND(C15=C16,COUNTIF(U$7:U$30,O16),COUNTIF(U$7:U$30,"<>"&M12))

I need to paste the drop-down calendar into a spreadsheet. Can you help me?

20 Views
0 Reply

I need to copy rows from one worksheet within a workbook to another based on values within one of the cells. The spreadsheet is an assessment data tracker I'm working on for my school but I'd like it to automatically filter the whole set of data based on

... Read More
25 Views
0 Reply

I want to use the formula Index to get a value from an external excel file.

 

I have one cell where I use Concatenate to create the reference to be used in Index.

For exemple :

A1 = "C:/MyFile/"    wich is the location of the file

B1= "Worksheet.xlsx"

C1

... Read More
51 Views
3 Replies

A bit of VBA code can do this.

Sub ResolveIndexForCells()
    Dim oCell As Range
    For Each oCell In Range("C1:C" & _
Range("C" & ActiveSheet.Rows.Count).End
... Read More

I have a spreadsheet and I am trying to add a IF Statement to work out if commissions are underpaid or Ok.  The IF statement works in the first cell, however, once I copy down formula it doesn't always provide accurate response (highlighted in red in atta

... Read More
73 Views
2 Replies

Martine,

 

In fact, the result of the formula is correct!

The values in column D have hidden decimals!

Select one of these cells and press Increase Decimal Button several

... Read More
Best Response confirmed by Blake T Walsh (Microsoft)

I would like to build a formula that says:

In M16 If the sum (F8:L13) > 40 then M17 = 40, If the sum (F8:L13) <or = 40 then M17 = F8:L13

Next:

If M17 > 40 then M20 = Sum (F8:L13)-40, If M17 < or = 40 then M17 = 0

 

Read More
51 Views
2 Replies

David,

 

a formula cannot change the value of another cell. So the formula will be in M17:

=MIN(40,SUM(F8:L13))

Hello, 

i need a quick help, is that possible to make the table header (or any cell out of the table range) to change dynamically based on the selected filter. 

 

example, one of the columns is "Martial Status" which has (Single, Married, ..etc). 

 

when

... Read More
34 Views
0 Reply

Hello everyone. I've a new problem I can't seem to find answers for and I'm hoping someone can help.

 

Cell A61 has a formula which returns the date "Tue 02-27" (2018 although the year not shown)

 

In cell M68, I'm trying to create a formula that will add

... Read More
124 Views
10 Replies
No, the formula was correct. Assuming the formula actually worked, my way would take the date in A61, (Tue 02-27), working with only the day (27), add 2 to it, (making th... Read More
Can you double check the formula? Is it really
=IF((DAY(A61)+2)="1",(A61+2),"Not")
or
you wrote it to
=IF((DAY(A61)+2)="1",DAY(A61+2),"Not")

I'm trying to sum some values on a worksheet, I downloaded the info from internet and it would allow me to change the info but whe I do the automated sum it woul give either 0 or an error and also I just noticed that it's not recognicing any of the values

... Read More
85 Views
5 Replies

Hi Jose

 

Thanks for the screen shots, it helps to see the problem!

 

From your first image, it appears that your Debito column is Text, therefore the SUM formula will no

... Read More

Hello,

I'm looking for some help with an INDEX MATCH MATCH formula across 2 sheets please.

 

Sheet 1: A front page (refs to other sheets have been replaced with values for the sample attached)
Sheet 2: A data table
I've attached a copy of part of the workb

... Read More
54 Views
2 Replies

Hi Tanisha,

 

Second MATCH doesn't work in your formula. In general it's not necessary to use absolute references, structured one work, like

=INDEX(Table8,MATCH(CONCATENA
... Read More
Best Response confirmed by Tanisha Perry-Warner (New Contributor)

I need to be able to quickly sort and filter a very large file, but I can't since the dates are in a non-standard format. How can I quickly change the format to a '01-NOV-17' date? It would take hours to change the several thousands of line items one by o

... Read More
42 Views
2 Replies

Meilani,

 

These dates are treated as texts so you have to convert them to real dates by using this formula:

=DATEVALUE(SUBSTITUTE(A1,".",":"))

 

This formula may give yo

... Read More
Can we have the sample of those non-standard date?

I have a data validation list in column C. 

 

In column D I have this formula:

=IF(OR(C$5:C$30="400 Confirmation Calls",C$5:C$30="1100 Inbound Calls",C$5:C$30="430 Appts Created"),1,"")&IF(OR(C$5:C$30="420 Confirmation Calls",C$5:C$30="1200 Inbound Calls"

... Read More
148 Views
9 Replies

It's a common issue!

 

Quick solution:

=SUMPRODUCT(VALUE(SUM RANGE))

 

Please refer to my answer to this question to learn more about this issue!

Read More

I am working on a spreadsheet that calculates a bonus based on multiple values: fixed cost, units and volume.  I have included the chart in which the bonus is calculated.  I am able to use an if function to calculate based on volume and fixed cost, but ad

... Read More
52 Views
1 Reply

You may need to create the bonus table in a new worksheet, e.g. BonusSchema

  A B C
1 Minimum Volume $500,000 $1,500,000
2 Minimum Units 4 9
3 Max Fixed Cost    
... Read More

I am trying to make a spreadsheet in Excel that will display multiple rows and columns if an IF statement is true. For example, we are making a machine that has different zones, and different parts associated with each zone. So if one machine has 10 zones

... Read More
30 Views
0 Reply

Hello, 

 

I'm making a brew sheet and am stuck. I'm decent with excel, but not this advanced.

 

In the sheet, I'm working on the formula for cell F11. The formula is complete, with the exception of my "if" statement. Right now, I have 0.75 about 3/4 the w

... Read More
67 Views
3 Replies

I am not sure if I understand your question fully. But this is how you would do if you want to multiple your equation by 0.75|0.9|1.25 depending on what is set on D3. I a

... Read More

I have a big file with just one column A the only thing in common is that each line has 133 characters on it. I try to use Text to Columns but did not work because the spacing is not the same. I try to count until the first empty character but did not wor

... Read More
96 Views
8 Replies

Hi Fernando,

 

Sorry, I didn't catch what you try to do - split on different columns or what?

And better if you attach sample file with few records.

 

Thank you

Read More

Hi,


I try to create an Auto weekly Gantt Chart for my project use. But I am stuck with the formula to change the cell color.

 

Pls refer to attach. 

Thank you

39 Views
1 Reply

I have attached a modified file.  I changed the worksheet name as "Example".  I also listed the steps in the worksheet.

 

Basically, I guess you want to show the dominati

... Read More

Capture.PNG

 

Dear Altruists,

Please help me remove this "Ghost Cell" from the excel sheet

Thanks in Advance

Read More
50 Views
2 Replies
Hi all.
I have been trying to create a formula for the issue below but to no success. Please coukd someone give me an idea of what formula to use. If A1 = 0,12,18,21 and A2 = 1,2,3,4,SR,HR and A3 = 3, 3.5, 4 then A4 = x (Predetermined figure).
56 Views
5 Replies

That could be like

=IF(SUM(--(A1={0,12,18,21}))*SUM(--(A2={1,2,3,4,"SR","HR"}))*SUM(--(A3={3,3.5,4})),"x","")

for A4 if you mean OR condition for values to check with A1,

... Read More

Hi All,

 

I've been working to come up with solutions for creating a dynamic drop-down for a study plan schedule.

  

I want the dropdown to depend on what "Term" it is and what "courses" are available in a degree.

 

There is a masterlist that has all cour

... Read More
50 Views
1 Reply

Hi,

 

Actually trying to figure out how to find the missing data from a data set. Have a data set which contains two columns, one has dates and the other one has time. Now i want to find if the hours are missing for any date. How do i do that.

Please help

... Read More
65 Views
3 Replies

Hi Aditya,

 

You may add Pivot Table with count of time as values and filter the value if it less than 24. Result will be list if dates for which some time missed.

Rainfall.JPG

If, i

... Read More
Best Response confirmed by Aditya Jadhav (Contributor)

Aditya,

 

There are no missing hours, each date corresponds to a time starting from 0:00 (12:00 AM) until 23:00!

To check for that, press the filter arrow of the time col

... Read More

Hello I'm stumped on this issue.

In excel I have a drop down list of Cartons like 100-199 200-299 300-399 etc.

That works fine except I have three other list beside it that I'm trying to get output to main page from carton selection of all the three other

... Read More
34 Views
1 Reply

Have you tried Index & Match formula?

Read More

I have 2 Columns that are time.  Example(one column(A) is arrival and the other column(B) is departures) I need them to highlight the row(A2,B2) when the difference between the two is greater than 15.  How can I have it highlight the row? 

Thanks.

I am ru

... Read More
92 Views
7 Replies

Joshua,

 

You can use Conditional Formatting with a formula to do that.

But please let us know the kind of difference between the two? Is it greater than 15 hours or minu

... Read More