Home

Formulas and Functions

892 Conversations

Latest Activity

Custom List Message Item

So here are the two IF functions I wish to combine together,

IF Formula 1 (Working)

=IF($D$20="Left Hands",SMALL(($F$2:$G$2,$F$3:$G$3,$F$4:$G$4,$F$5:$G$5,$F$32:$G$32,$F$33:$G$33,$F$20:$G$20,$F$21:$G$21),COLUMN()-COLUMN($I$2)+1),SMALL(($F$2:$G$2,$F$3:$G$3,

... Read More
67 Views
4 Replies

Hope,

 

to produce shorter formulas, which are better to evaluate, it would be better to change the model so that you can handle the data in contiguous arrays.

 

Read More

Hope,

 

what are the possible values in D20 and D30?

 

I've been searching everywhere but can't find a solution to this in the right context.

 

I'm trying to nest an Index/Match array inside an IF statement with AND, like so...

 

=IF(AND(E35<>"",E35<>(INDEX($F$35:$F$45,MATCH("*"&E35&"*",$F$35:$F$45,0)))),E35,

... Read More
99 Views
5 Replies

Amir,

 

What's your goal is?

Please provide us with a sample of your data!

Checking online I've found these two formulas:

=IF(COUNT(SEARCH({"Avenue","Road"},D2)),"TRUE","FALSE") - This returns TRUE or FALSE if either word is anywhere in the cell. 

=RIGHT(D2,LEN(D2)-FIND("*",SUBSTITUTE(D2," ","*",LEN(D2)-LEN(SUBSTITUTE(D2," ","")

... Read More
168 Views
18 Replies

Hi, Patrick Silverwise.  

 

  1. Use Name Manager to create a name, theValue
    =$D2
  2. Use Name Manager to create a name, LastWord
    =RIGHT(theValue,LEN(theValue)-FIND("|*|",SUBSTI
... Read More

Patrick,

 

{=OR(IFERROR(LEN(A1)-SEARCH({"Avenue";"Road"},A1)+1={6;4},FALSE))}

Hello,

 

I am interested in creating a daily and weekly meeting schedule that resembles a meeting calendar you can print from Outlook, Gmail, etc. I couldn't find an existing Excel template. I have found two templates that I think will work individually,

... Read More
30 Views
0 Reply

image.pngimage.pngFirst I get the top message even though I have macros enabled. When I disable the add-in from the developer tab I am shown this message. I have followed the help button and it has not helped. This is on Windows 10

Read More
28 Views
0 Reply

I have an array.  and want to search a specific column for a value greater than 0 in the array.  if i find a value greater than 0 i want to return the text entered in a different column of the array.  See below for a picture.

 

i want to search column lab

... Read More
69 Views
6 Replies

Hi,

 

It could be like

=IFNA(INDEX(<category column>, MATCH(<number>,<label column>,0)),"")

Is there are simple formula I can use that counts the number of times a particular letter appears in a list?

For example, S = Sick, how do I calculate the number of times this appears throughout the year. 

47 Views
3 Replies

Hi Jennifer,

 

You can use COUNTIF function as shown in the below screenshot:

 

COUNTIF function.png 

Read More

Hi ,

 

UK user here.

 

I have a spreadsheet to track sales and income and we have recently registered for VAT so I want to add a drop down box to mark yes or no for VAT  purposes. 

 

I've got the formula worked out to calculate VAT but how do I get Excel

... Read More
39 Views
1 Reply

Update;

 

Columns C-G are entries for expense amounts and it will extend over 25-30 rows.

 

Column I

=SUM(C40:G40)*0.2 (to work out VAT amount)

 

Column

=SUM(C40:G40)-I40

... Read More

Based on what is below. I need a formula so that if there is a NO in any of the rows b1:I1, A2= B2+(row with no). In the example A2= Yellow,Goat. I would be satisfied with 2 formulas. One that if there is a NO in the row a1=yellow. Also if there is a NO i

... Read More
47 Views
2 Replies
would uou mind editing your question to clarify the row numbers.
"I need a formula so that if there is a NO in any of the rows b1:I1" Row 1?
" One that if there is a NO in ... Read More

 

I would like to transfer information from one sheet to another, such as.

 

If I have sheet 1 with A1 = Chad, B1 = 1, C1 = 2, D1 = 3; sheet 1 with A2 = John, B2 = 4, C2 = 5, D3 = 6

 

If I type John in A1 on sheet 2, how do I get B1, C1, and D1 to auto po

... Read More
35 Views
2 Replies
Thanks for the help Willy Lau

Did I answer your question? or you want something else?

Hello!

 

I just started a new job and am taking over this massive spreadsheet and have been asked to make some updates. Right now I have three separate sets of payments with payment amount, due date, payment date, etc. on a sheet. The following formula

=I

... Read More
31 Views
0 Reply
This short keys will surel helps you to get your work done quickly, hope you will find this article helpful !
https://urbanewanderlust.blogspot.in/2018/02/microsoft-excel-shortcut-keys.html Read More
54 Views
1 Reply

Not clear for which version of Excel, which locale. IMHO, better to use original

https://support.office.com/en-us/article/keyboard-shortcuts-in-excel-for-windows-1798d9d5-842a-42b8-9c99-9b7213f0040f

Read More

Hi.    I have a very simple formula: =TEXT(G3,"mmm. Dd")

 

and the output is unfortunately 07. פבר

 

The language settings (under Options/Language) are set to English.  I've tried formating the individual cells to English - no dice.  I have a new operatin

... Read More
27 Views
2 Replies

Hi Gittel,

 

Check your local if it's one of English

Locale.JPG

or you may specify locale directly in TEXT format like

=TEXT(G3,"[$-409]mmm. dd")

for US one.

Read More
Best Response confirmed by Gittel Marcus (New Contributor)

Hello,

 

I am trying to get it so when I put Yes in the Connected column, it will add the PSU # from column G into the box labeled Completed, put the average of PSU that has been Connected in the box labeled BSI, and add the CHL from column F that has bee

... Read More
43 Views
1 Reply

Hi, Would there be multiple connected items i.e. multiple rows with 'Yes' in Column L? If yes, what would want in the Completed & CHL sold boxes: Sum, Count or Individual

... Read More

Hi,

I have a column of data that I want to format to be bold when it is greater than 5000. But, in it I have numbers that are <1000 and written this way as well as numbers that include 0.010 I or 0.010 U. If they contain the letters or less than symbol I

... Read More
33 Views
1 Reply

Hi, If all the values are in the same column you'll have to set-up multiple conditional formatting rules to capture all cases. You can do this by selecting the data colum

... Read More

HI, I'm new to excel formulas and conditional formatting!  I'm using a spreadsheet to track new hires, and I'm trying to figure out how I can use "less footsteps" and not have to apply conditional formatting to every.single.cell. in a column.....  I'd lik

... Read More
40 Views
1 Reply

From your screenshot, I do not know when "New Hire" and those column should be highlighted.  

 

I think, if you are new to excel, you may not have the ideas of "Relative Reference", "Absolute Reference" and "Mixed Reference"

... Read More

I am having trouble trying to come up with a function or IF statement that will input an "x" in a cell on the side indicating that the row has a colored cell in it. The colored cells already have conditional forming applied to it that tells the cell to co

... Read More
64 Views
3 Replies
You may put the same formula of cknditional formating to the column m. if(that_formula,"X",""). Of course, you may need to change a bit in the formula if necessary.
Read More

I have a workbook with 4 sheets (Search, Active1, Active2 and Inactive).  Search is where the user will enter an NPI# to search the other 3 worksheets for EVERY appearance of that NPI#, presenting data from selected columns of the row that NPI# is on.  On

... Read More
78 Views
8 Replies

Sorry...left off the attachment

Hi,

 

there is no attachment.

 

Hello,

 

I need a little nudge on a formula.

 

I am working on a spreadsheet that calculates day rates for my business. 

 

Let's say a day is ten hours and the rate for that day is 1000 dollars. Each day requires the transport and setup of multiple pieces

... Read More
151 Views
12 Replies

In addition to Willy's answer, see if this formula works for you.

 

=IF(MOD(B3,1),SUM(INT(B3)*1000,650),B3*1000)

 

Basically, I've devised an IF statement that checks to

... Read More
Hi Randy

Are you able to upload a sample of the spreadsheet? Might be good to see what you are working with!

Cheers
Damien
=(INT(quote)+MAX((MOD(quote,1)>0)*0.65, MOD(quote,1)))*rate

quote can be 1,1.5, 1.7, 2, 2.1, etc.

rate should be 1000, or the absolute cell reference of the cell contains

... Read More

Hi there,

 

I am looking to find the easiest formula solution for the following.

 

I wish to check if table a5:a32 contains the word "MTD", if so, it takes data from a cell in the same row from column F and then sums it in a seperate cell.

so if i had 5 M

... Read More
68 Views
3 Replies

Hi Steven

 

If I am understanding you right, try this formula:

 

=SUMIF(A2:A5,"MTD",B2:B5)

 

Change A to your desired column where the MTD text is located.

Change B to wh

... Read More
Best Response confirmed by Steven Elphick (New Contributor)
Hi,

I have two columns containing serial numbers.

I need a function which states that if the first column ends in either 616 or 796, take that column’s serial number and if it ends in neither, take the second column’s serial number.

Is there a function I can... Read More
73 Views
3 Replies

Hi Judy

 

You'll need a formula to do what you are proposing.

 

I've drafted this formula, see if it does the job for you?

 

=IF(OR(RIGHT(A2,3)="616",RIGHT(A2,3)="796"),A

... Read More
Best Response confirmed by Judy Tran (New Contributor)

Hi, I'm wondering if there is a function/formula that I can apply to an entire column that will add entities on a row by row basis.  I want my cells in column Q, Q5 for example, to give me the sum of N5-O5-P5.  I can apply a sum formula for Q5, Q6, Q7, et

... Read More
81 Views
5 Replies

Kevin,

 

here is one way to do it:

=SUM(N:N)-SUM(O:O)-SUM(P:P)

Hi, I'm using a simple search function "vert.zoeken" to retrieve a value from a second excel worksheet. If this value (in the second worksheet) is changed afterwards this must not be recalculated in the original sheet.

Is there a solution for this?

 

than

... Read More
24 Views
1 Reply

Hi Paul,

 

If you want the values you've retrieved don't change if the original values have changed, then you have to get rid of the formulas and keep the values of these

... Read More

                                     You are subscribed to this thread

I  have been playing around with a problem for a few days, if you could help me to derive a solution, it would be great!
  Resource 1 Resource 2 Resource 3 Resource 4 Resource 5 Resource 6 Resource 7 Resource 8
... Read More
105 Views
3 Replies

Assumption:

  1. your table on A1:AY501 in sheet1, where project column on column a, and resource headers from B1 to AY1.
  2. project selection cell on A1 in sheet2
  3. resource list
... Read More

Good Morning

 

I am using the following formula and would like to format the results based on the results of the formula.

=IF(C2<'2-1'!B2,"u",IF(C2>'2-1'!B2,"t","v"))

 

When shown in Marlett font, the "u", "t", and "v" show in arrows and I need to format

... Read More
68 Views
5 Replies

Alternatively you may use built-in into conditional formatting arrows icons set using formula for rules (and show icons only if desired)

IconSet.JPG

 

Read More

Hi Tonya Troxtell, you don't need to base on the result of the arrow.  You just need three conditional formatting rule on the arrow cells to base on your formula

  1. for u
    =
... Read More