Home

Formulas and Functions

297 Conversations

Latest Activity

Custom List Message Item

Hello,

 

I am struggling to build a formula to look up a specific value on one sheet by comparing the people's last names, and comparing codes. For example, on sheet 1 codes I use are 6LIFE and 6LIFEO which correspond to the LIFE column on sheet 2. 

 

Here is

... Read More
125 Views
10 Replies

Hi Monica,

 

Please clarify bit more. For the same last name you'd like to find the record where the value is second column (Code) of the Sheet1 matches the name of the col

... Read More

Hello,

I am using excel and forgot the formula which I can employee to find the cells and calculate them so i get my desired amount. I have the total amount already but don't know which cells are equal to that amount. Kindly some body tell me how can I fin

... Read More
57 Views
4 Replies

Hi Asad,

 

If simply find the cell you may filter your range or use Find from ribbon menu with Look in Values options. Or you mean something else?

Hello everybody, I've got a problem with developing a function that will, choose the bigest value from the rows fields and as the outcome will show the name of the column in F column. Maybe any of you have any idea how to solve that. Best regards. The exa

... Read More
38 Views
4 Replies
Exactly, thats what I need :)

Hi Bartosz,

 

Do you mean the max value in the row for columns from B to E and return in F (same row) the name from B1 to E1?

I'm trying to create a form with Province, City, District dropdowns being dependent on each other. I was able to create the dropdown for the provinces and make city dropdown only populate cities within the selected province, but when it comes to districts

... Read More
34 Views
1 Reply

Hi Francis,

 

Here is an example with 3 dependent lists (Country, Region, City), i guess close to your case

http://www.contextures.com/xlDataVal02.html

 

Read More

Hello everybody,

I need help with adjusting my formula, in attached file you'll see that in some places in last column I have blank places. I need adjusting my formula to one conditions, if value in every field D,E,F is 0 than as an output show "100%". In

... Read More
33 Views
2 Replies

Hello,

 

please check your conditional formats in column I. The first conditional format compares the values from the range to cell =$J$2814 and sets the format to ;;; mean

... Read More
Best Response

Hello,  I'm trying to calculate the difference in a column of cells from the number 40 when the number is greater than 40 and then get the total of the differences.  I can't seem to group or nest the right formula to achieve this.  Any help?

Read More
39 Views
2 Replies
Suppose your numbers are in A2:A200:
=SUMIF(A2:A200,">40",A2:A200)-40*COUNTIF(A2:A200,">40")

I use Etrade to deliver real time stock information, therefore the cells change throughout the day. I want to capture a stock value at a certain time of the day. I have the time part setup but the cell continue to update with new information. But I want s

... Read More
14 Views
0 Reply
Is it possible to create a data validation list that auto populates after selecting a specific item in a separate data validation list? Let's say I want to select fruit or vegetable in the first list, then I want the second list to auto populates saying e... Read More
116 Views
6 Replies

Hi Jacob,

 

You can create two separate ranges listing fruits and vegetables, and assign range names to them exactly matching the values in you higher level list (i.e. Frui

... Read More
Best Response

Hi,

I'm having trouble with a formula in my aging spreadsheet.  I have the 3 columns below and i need aging to track in the 3rd column.  Once we submit a change order and input a date in the 1st column, i need for the 3rd column to start the aging, but onc

... Read More
28 Views
2 Replies

Hi Steve,

 

If you'd like to find difference in days between approval and submission dates using current date if there was no approval, when like

=IF(G2>1,G2,TODAY()) - F2

Ab

... Read More

Hello, 

 

Thanks in advance for any help!

 

I am trying to set some conditional formatting which I know how to do, but when I enter what I want it to mark the cells as it includes cells with no entry. 

 

What I am trying to do is condition due date cells to a s

... Read More
49 Views
2 Replies

Hi Penny,

 

please find attached my suggested solution. I have created extra columns for each condition, and used LEN() function to capture empty cells (as an alternative,

... Read More

My problem is I want to combine two columns together that have city and state but i also need to add a comma and a space in front of the state  this is for like 6000 plus cells, i would like to be able to do this all at once or add comma and space first f

... Read More
163 Views
7 Replies

Ryan,

 

use Flash Fill.

 

Thanks for all the help ended up using =concatenate(B:B,C:C) and then scrolled over D column hit ctrl+enter and wala i ended up using the same formula to add the comma an

... Read More

Hi Rayn,

 

Use this formula:

=PROPER(A2)&", "&UPPER(B2)

EX.JPG

Read More

Hi Ryan

 

=PROPER(A1)&", "&UPPER(B1)

Hi folks,

Maybe the quesiton in one sentence is not clear. I am trying to extract 4 digits from a list of cells, but because some of the digits have 0 infront of them. So in the new column when I am only use the "RIGHT(A3, 4)"formula the 0 is not showing u

... Read More
44 Views
1 Reply

Gabriella,

 

if the value in cell A3 is a number then it would show as 987. But if you apply a number format like 0000 then it shows as 0987.

 

=RIGHT(TEXT(A3,"0000"),4)
Read More

Do custom functions work with Excel 2000? I can write a function, and Excel seems to recognize the validity of the word Function, but User Defined doesn't appear in the Function Category list, so I can't actually use the function. What am I missing?

Read More
35 Views
0 Reply
I want to make a general ledger from american journal using somw functions that i will send to whoever will help me and i will send the file too and those are the functions Vlookup
Count
Count a
Count if
Count ifs
Sum
Sum if
Sum ifs
W f data validation indirect a... Read More
81 Views
7 Replies

Sorry, but your question is not clear. It sounds as if you want somebody here to work for free on a fairly large spreadsheet project. That is not what we do here.

 

If you

... Read More
I am intrigued..... what does W f data validation indirect aw data validation 3mtn bs deh ana 3amlha 3shan ageb asamy el 7sabat fo2 3l ymen . mean? Also am i correct in s... Read More

Hi guys!

 

I'm currently in an internship and I need to update a file. Basically what I need is the possibility to make the sum from sum(M6:Q6) into a changing sum depending on the starting point (ie that it automatically starts at the right columm so that

... Read More
64 Views
4 Replies

Hi Virginie,

 

Use OFFSET, like

=SUM(OFFSET($F$4,$B$1-1,B10,1,12-B10))

where instead of B10 it will be VLOOKUP for it's calculation. I prefer INDEX/MATCH, but that doesn't ma

... Read More

 

Hi guys,

I have a big problem, I'm on internsip and I've gotten a task to develop an excel file which will search the data base and take out the names of companys that stopped making transactions. For instance if one company made a transaction in 1,2,3 mo

... Read More
292 Views
29 Replies

 Hi @Bartosz Szymański,

 

Your problem can be solved with a pivot table. Put the [Value Date] in the Values and [Customer] in the Rows. Then change the valuesettings to Max

... Read More

Hi @Bartosz Szymański,

 

I just saw your sheet. You want to know the which company stopped transaction.

 

I need some clarification. Then only i can solve. 

1.Company name col

... Read More

I am trying to multiply row 2 by row 1 and then sum the results. BUT, I want to start the multiplication of row 2 at the start of row 1.image.png

 

 

I have tried sumproduct on its own and using CSE, but I think I am not using the correct formula. Any ideas?

Read More
35 Views
2 Replies

Hi Richard,

 

That could be something like this

=SUMPRODUCT($C4:C4,N(OFFSET($C5:C5,0,COLUMN(C5)-COLUMN($C5:C5))))

 

Best Response

Morning All! Yes I'm A Newbie so I'm sure you've all seen this question before!

I'm looking to select a cell series (eg: C1:C17) to check for a term "ardvark". If ardvark is present in C1 I want the destinationcell to report A1", ".  If not, then do nothin

... Read More
34 Views
1 Reply

kscott,

 

=TEXTJOIN(", ",TRUE,IF(C1:C17="ardvark",A1:A17,""))

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
32 Views
1 Reply

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

Hi friends, I am new here.

I have a list of checks and I need a formula that can choose from that list the most appropriate checks to make a payment (detailing which is the wanted result), trying to minimize the use of cash. I think that can be done with s

... Read More
26 Views
1 Reply

Hi @Franco Marrello,

 

Not able to understand it clearly.

 

So please expalin more.

Read More

Hello and Thank you in advance for any assistance! My name is Tobey, and I working on a spreadsheet in Excel. I am trying to work o a spreadsheet, but I am having trouble with one of the columns. I need to get the percentage change in patients seen betwee

... Read More
46 Views
1 Reply

Tobey,

 

that should be basic mathematics, shouldn't it?

 

=Patients_Feb/Patients_Jan-1

Dear Friends,

Please help me out for the below issue. I have a DATEDIF value, and on which the IF condition is not working. But if I change the DATEDIF value to numeric mannually then the IF function is working.

 

DATEDIF value:

=DATEDIF(D3,TODAY(),"Y")&"."&D

... Read More
51 Views
4 Replies

Riyaz,

 

I would solve it this way.

=LOOKUP(DATEDIF(D3,TODAY(),"M"),$H$1:$H$5,$I$1:$I$5)


Solution_Riyaz.PNG

 

 

Read More
Best Response

Hi Riyaz,

 

You compare text value which is returned by first formula with some numbers. In logical operations any text is aways "bigger" than any numbers, e.g =("0" > 1) r

... Read More

Hi ! 
My Worksheet image is in Below Link. I also attach excel file.
My Excel Sheet Image

 

  1. Information About Competitions situation: I am a school teacher. The worksheet is about to practical "Yogaasana" test of students'. In this practical exam there are fo
... Read More
77 Views
6 Replies

Jignesh,

 

my solution is in the same direction as Sergei's.

 

Hi Jignesh,

 

I'm not sure if you need to automate 1) and especially 3). As for 2) you may create one more Index like

=(2nd Highest)*10000 + (Grand total)

and put places base

... Read More
Best Response

I have a simple spreadsheet in Excel that I use for mileage. My column that I am adding is not adding correctly, I have reentered my formulus, I have used my sum for the end column and it still continues to be 2 cents off. I have ran all the issues, and c

... Read More
67 Views
6 Replies

It looks like typical "few cents gap" accountants issue. What they do to avoid it that's round all intermediate calculations. And that's what we have in real life - we do

... Read More
by pages I mean workbook tabs of same but named different
That's what I thought but its not doing it on all my pages. and what do you mean number format? I have tried accounting, currency and number .

Laurie,

 

it sounds like a rounding or formatting issue.

What number format are you using?

 

And the SUM() function in your formula is totally superfluous.

 

 

 

Read More

necesito identificar los tìtulos (texto en negritas y mayusculas) de un archivo de gran dimensión (con interválos de filas variables). Segundo, ya identificados necesito devolver ese tìtulo en cada uno de los renglones pertenecientes a ese título, pero di

... Read More
22 Views
1 Reply

Hi @Rafael Flores,

 

Utilice la función "Freeze Panes" en Excel.

Si desea imprimirlos en cada página, utilice "Imprimir título"

Vea las imágenes.

 

Untitled5.pngUntitled6.png

Read More