Home

Formulas and Functions

369 Conversations

Latest Activity

Custom List Message Item

Help with a nested if statement?  Want the statement to be dependent on column D.  If column D is yes, and B or C is >  0 then amounts would move to new co in column G&H.  If column D is no and B or C is >  0, then amounts would stay with old co in column

... Read More
15 Views
1 Reply

Hello,

 

in cell E3 use

 

=IF(AND($D3="no",B3>0),B3,0)

 

copy across to cell F3 and then copy down. In cell G3 use

 

=IF(AND($D3="yes",B3>0),B3,0)

 

copy across to cell H3 and copy

... Read More

I have attached a file with data to explain the formula(s) I am seeking. In brief, how to count a range of grades in one formula and whether a range of grades can be counted by cell color.

36 Views
5 Replies

Thanks Sergei!

Also, the example showing Column R with text in colored cells. Is there a formula to count the cells by color?

Hi Tom,

 

That could be

=SUMPRODUCT(($F$5:$F$20>=LEFT($I22,1))*(($F$5:$F$20<=RIGHT($I22,1))))

if your range is in I22 as

a-d

Attached

 

Read More

Hello, 

Scenario: 
If A1 is blank, try to get data from B1. 
If B1 is blank as well, try to get data from C1

If A1 is not blank, continue to use the data from A1. 

I have =IF(ISBLANK(A1),B1,IF(ISBLANK(A1,C1,A1)) 

It's filling in my blanks from column A with col

... Read More
19 Views
1 Reply

I'd suggest

=IF(ISBLANK(A1)*ISBLANK(B1),C1,IF(ISBLANK(B1),A1,B1))

- start from the end

 

Can I use IF function (yes/no)  if one of the cells already has sum formula?

32 Views
5 Replies

If your IF function will be in another cell - yes, why not

Hi. I'm pretty much new to excel and I need a (custom?) function that returns several values to one cell:

 

- I have a file (File1) with product codes in column B
- I have a second file (File2) with corresponding product codes in column C
- I need the functio

... Read More
34 Views
1 Reply

Um... I guess this is what you wanted, chained if depending on your data.

 

=IF(ISBLANK(INDIRECT("[FILENAME.XLS]Sheet1!$F"&MATCH(B2,[FILENAME.XLS]Sheet1!$C:$C,0))),"B2C CO

... Read More

Is there a way to concatenate and keep the currency formula at the same time. I  have to list a dollar range from $25- $10,000 in $50 increments. I've tried to copy the numbers down Excel in 2 columns then concatentate, but it looses the "$" symbol and th

... Read More
27 Views
2 Replies

Hi Lisa,

 

To concatenate convert numbers to text like

=TEXT(A1,"$#,##0") & " - " & TEXT(B1,"$#,##0")

or FlashFill could work depends on how your data is structured

 

Read More

So I have the following formula working fine, BUT what I am trying to do is change the return values, where return value "1" is a second "IF" statement.

 

 

=IF(ISNUMBER(MATCH(Schedule!B2,Schedule!A1:A2340,0)),1,0) (this works fine) but I want the value one

... Read More
19 Views
1 Reply

Greg,

 

remove the equal sign before the second IF.

=IF(ISNUMBER(MATCH(A39,A2:A131,0)),IF(C40="win",C449,"X"),0) 
Best Response

Please help! I'm working on updating an advancement tracker for cub scouts. The worksheet uses the COUNTIF function to count the number of cells that have an A to determine whether to mark the adventure C for complete or P for partial. A simple example: C

... Read More
100 Views
11 Replies

Hello,

 

it's probably best to see this in a sample file. Please mock up a few scenarios and the expected results. The final setup may require some helper columns where the

... Read More

Hi, i have a problem whit this function..... i have a table and in the first column i want a correlative that count the number of times that a cod_id exist in another column but in the specific row that im working.... so i used this function:

 

image.png

 

but when i

... Read More
37 Views
4 Replies

Diego,

 

there is a bug with expanding ranges.

 

Running Total in a Microsoft Excel Table

Read More
Best Response

Hello! I've used the Sum function many times to quickly calculate the sum of a bunch of cells and I'm currently trying to do that for my finance spreadsheet.

 

Problem is when I add up the cells (ex: =Sum(C3, C10, C29)) the total comes out to be $0.00 every

... Read More
77 Views
3 Replies

Hi Alessandra,

 

How cells in your finance spreadsheet are formatted? If, for example, you enter manually in C3 the $10 you have the text string here. If you enter just 10

... Read More

I am trying to create a few formula's for the following

 

# of New Boxes QAed:     >>>>0# of Those Boxes Failed QA:     >>>>0
          
Failed Box #'s:Part #:PG #:Reason For Failure:
    
    
    
    

 

What I want to do is get information from the bottom sheet

... Read More
15 Views
0 Reply

Hello,

 

Below is my original post but I think my inquiry is not possible.  Instead, would anyone know how to use a formula to identify a number is a group of text and then execute a text to column for the next 40 or 50 characters?

 

Thanks for your help!

 

Don

... Read More
60 Views
3 Replies

Hi Don,

You may find positions of the first and last numbers in the string and extract what is between as an address. But that only works if you have no other numbers with

... Read More

I am using Jet reports and I am trying to find out how I can conditionally hide sheets

29 Views
0 Reply

Hi experts!

 

I'm an Excel NOOB and have hit a brick wall here - hope somebody can help:

 

I have a list in Excel with people on Twitter who are important to my company. I have all kinds of data on them, so I can filter them in numerous ways - one of them bei

... Read More
246 Views
15 Replies

Definitely use the concatenate function or the & sign.

You can do something like this:
=URLpart1&$F$2&URLpart2&$F$3&URLpart3&C2&URLpart4

With the concatenate functions it wo

... Read More

Hi Niels,

 

The most complex part of your task is to combine <from> part of the search string - we have to combine Twitter handle column in one string based on criteria are

... Read More
Best Response

Hi, 

 

I have a table containing children who are enrolling for instrument tuition. I have a column set to Y (Yes) is a child returned the slip from the letter, and another set to Y, when they have made the online payment. I want to have a rule to highlight

... Read More
46 Views
2 Replies

Hi Cecilia,

 

First, better to apply your rule not to entire column but with for some range with gap within which you data will be. Another option dynamic range or table. O

... Read More
Best Response

I Need some help with a VLOOKUP Formula. 

I have a spreadsheet where you dump results from samples. However sometimes Samples are tested twice. 

So I need a formula that will look up the Sample ID from one sheet, and If there is two results for that sample,

... Read More
58 Views
3 Replies
Thank you both! These have helped a lot!

Emily,

 

another lookup variation:

=LOOKUP(9^99,INDEX($H$3:$I$4,MATCH(A3,$G$3:$G$4,0),0))

Hi Emili,

 

For the date located as on your screenshot you may use something like

=INDEX($G$3:$I$10,MATCH($A3,$G$3:$G$10,0),3-(INDEX($G$3:$I$10,MATCH($A3,$G$3:$G$10,0),3)="-
... Read More

Hi, i can't solve this and i can't even search for the answer cause i don't know how to explain it right.

On the base of 2 answers (green), it has to give a result (yellow) took from a matrix.

Please help me :-)

47 Views
2 Replies

Michel,

 

that is a two-way-lookup. It can be done in many ways.

 

=LOOKUP(2,1/(K12:K15=G13)/(L12:L15=H13),M12:M15)
Best Response

I was looking at the personal monthly budget that comes with excel 2016 and it has cell program where it uses the programming: 

 

15001400=[Projected Cost]-[Actual Cost]
60100=[Projected Cost]-[Actual Cost]
5060=[Projected Cost]-[Actual Cost]
200180=[Projected
... Read More
40 Views
2 Replies

Hi Rick,

 

I don't know this template, perhaps the formula looks like

=[@[Projected Cost]]-[@[Actual Cost]]

If so that's the Excel Table with structured references

Read More

excel 1.JPGexcel 3.JPGexcel 2.JPG

This worksheet has lots of big formulas and we have tried to copy the previous month over to use this month but they arent all working properly and I am stuck! No one here can figure it out as he was an Excel whiz. We've tried to copy and move to end- tha

... Read More
78 Views
4 Replies

Unfortunately, you really haven't posted enough to go on. Can you see the (Hide) sheets? Have you tried auditing the formulas with the Evaluate tool (Formulas > Evaluate

... Read More

Thank goodness I posted in a Microsoft Tech community and had over 20 people look at this and not ONE can give any type of an answer! smdh

I can even email the previous worksheet to someone if they are willing to help me. I just need it all copied (all pages) and sent back clean WITH all formulas in place. A

... Read More

I'm trying to set up a Diary in Excel where I can create a new sheet each day and the date will automatically convert to be 1 day ahead of the following sheet. the formula works but the date only changes to 1 day after the original sheet Should be NEXT SH

... Read More
39 Views
0 Reply

Hi, 

 

I am trying to colour code a spreadsheet to show if the cell has increased or decreased since last month. How to I first get the cell to look up the correct name and then look at the correct cell to compare if it has increased or decreased?

Read More
19 Views
0 Reply

I have looked for a solution to this at no avail.

 

I have two variables that create number D31(15)*F18(94.98)=1424.7

 

Based on this answer I have two whole numbers multiples I can round this answer up or down to as long as the answer is within 10% of the wh

... Read More
59 Views
4 Replies

Good morning.
I'm using Excel 2013.
I would like to know if there is a possibility to prevent the deletion of individual cells, but it is possible to delete the entire line.
Thank you,
Matteo

42 Views
3 Replies

Hi,

 

If you mean to delete the row with protected cell - nope, that's not possible. If the row is in another range - yes. More about the options is here https://support.office.com/en-us/article/Lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a

... Read More

I need to come up with a formula that will do this"=IF(AND(F34=Sheet2!B3,F36=Sheet2!C2),Sheet2!C3,0)" multiple times. For example, If F34 shows B Business and F36 shows lA then show C3. F34 and F36 are drop down lists. I manually entered a nested If(and)

... Read More
38 Views
0 Reply

I'm attempting to create a count of wins for participants in a pickem league.  For instance, I have  a list of picks for made by John in column D.  I have the winner of each matchup in column C.  I want to count the number of times for the week that John'

... Read More
36 Views
2 Replies

Maybe not the most elegant solution, but this should work:

 

=SUMPRODUCT(1*(C:C=D:D),1*NOT(ISBLANK(D:D)))

 

It compares column C to column D and converts the resulting range 

... Read More