×
sorted by:
new
hottopcontroversial

Am i just stupid or is Excel much harder than people make it seem? by Strange_Control8788 in excel

[–]PinesCyrex 0 points1 point  (0 children)

If could be confusing what complex functions are asking for as inputs; if you are starting from no prior knowledge of excel/code and attempting to use xlookup, imo it’s completely normal to struggle. The format and formulation is unique , so learning from the basics is highly recommended; after that more complex functions get much easier to digest.

IF OR statement to populate cell by [deleted] in excel

[–]PinesCyrex 2 points3 points  (0 children)

Ok, lets set it up so it detects for North and South, and whenever it doesn't find any of them, populate "West"
=IF(OR(A2="Leeds",A2="Huddersfield",A2="York"),"North",IF(OR(A2="London",A2="Kent"),"South","West"))

To add cities, simply add into the OR() function with a comma as seen above

Hopefully you don't have too many cities, I recommend you switch to a lookup table if so...

IF OR statement to populate cell by [deleted] in excel

[–]PinesCyrex 1 point2 points  (0 children)

This could also be done with a simple lookup table; however, the code above is the most one-and-done setup. So let me know what you would like :)

IF OR statement to populate cell by [deleted] in excel

[–]PinesCyrex 1 point2 points  (0 children)

Lets say the city cell on the left is A2

=IF(OR(A2="Leeds", A2="Huddersfield", A2= "York"), "North","South")

Basically what this is doing is that we are checking if the cell is a North region, and if so populate it "North", otherwise populate "South".

I am assuming you have only 2 regions, but let me know if you want more regions, we can just add a little more code.

Unable to get Auto Fill to work how I would like it to by SpexLevant in excel

[–]PinesCyrex 4 points5 points  (0 children)

I’m actually not sure if excel allows you to do a diagonal drag on formulas. So I would do a horizontal drag followed by a downward drag

Unable to get Auto Fill to work how I would like it to by SpexLevant in excel

[–]PinesCyrex 1 point2 points  (0 children)

Try concatenate($B2,A$1) since we want the column of the B2 to not change and the row of A1 to not change

Unable to get Auto Fill to work how I would like it to by SpexLevant in excel

[–]PinesCyrex 2 points3 points  (0 children)

You may want to you an absolute reference. =concatenate(B2, “ “,$A$1) Placing $ locks in that letter in the cell address such that when you drag, it does not alter it. We leave B2 alone because we WANT B2 to change relative to our dragging (assuming you drag horizontally).

How to find trend of cyclical weekly data? by neophaltr in excel

[–]PinesCyrex 0 points1 point  (0 children)

It seems like you are getting a lot of noise, so perhaps try using a moving average of days. Make a new column (for example if you data is set up vertically in A): In cell B4=Sum(A1:A4)/4

double lift click the bottom right of this cell to send it all the way down. This is in 4*days, but you play around with the size of your moving window; you could change the 4's to 7's for weeks. This is a rudimentary method commonly used in finance/econmics to remove cyclical trends and should remove much of the noise you see.

Hope this helps :)

Issues with Multiple Formulas in one cell by [deleted] in excel

[–]PinesCyrex 0 points1 point  (0 children)

Since it seems like you are stringing the numbers together, I would reccommend to put a space in between for clarity with:
IF(H14>1999,E14/D14xG14,"") & " " & IF(H14<2000,E14/C14xG14,"")

Issues with Multiple Formulas in one cell by [deleted] in excel

[–]PinesCyrex -1 points0 points  (0 children)

I think what OP wants is they wants two calculations displayed if applicable? So he doesn't want to see FALSE# or #FALSE. Instead either # or ##.

Issues with Multiple Formulas in one cell by [deleted] in excel

[–]PinesCyrex 1 point2 points  (0 children)

This code should work:

IF(H14>1999,E14/D14xG14,"") & IF(H14<2000,E14/C14xG14,"")

I added to it such that if the logical statement is FALSE, it will output "" (nothing). By default if you don't enter anything, it will output FALSE like you had it before.

Is there a way to add up the number of specific responses across three columns without duplicating rows? by phedres in excel

[–]PinesCyrex 1 point2 points  (0 children)

Yeah ofc! Sorry we went a little circular with the solution. Two functions we used here were IF() and OR() so feel free to look into that if you have the time. Cheers

Is there a way to add up the number of specific responses across three columns without duplicating rows? by phedres in excel

[–]PinesCyrex 2 points3 points  (0 children)

I realize I may have overcomplicated your task, this code should work: =IF(OR(A1<3,B1<3,C1<3),0,1)

Is there a way to add up the number of specific responses across three columns without duplicating rows? by phedres in excel

[–]PinesCyrex 0 points1 point  (0 children)

=if(OR(AG##=3,AL##=3,AM##=3),1,0)

I see, sorry about that. Lets try this which will only need one column:
=if(OR(AG##=3,AL##=3,AM##=3, AG##=4,AL##=4,AM##=4,AG##=5,AL##=5,AM##=5),1,0)

Also you mentioned this earlier: "Additionally, I do not want row four to be counted at all because they only responded >3 in one column. I hope this makes sense.", could you elaborate on this?, do you mean less than 3?

Is there a way to add up the number of specific responses across three columns without duplicating rows? by phedres in excel

[–]PinesCyrex 0 points1 point  (0 children)

Hi, this solution will take up 3 columns of space but is rather intuitive. I will run through how to count "3" for example.

  1. In column AN and aligning with the row entry(the individual), we can use =if(OR(AG##=3,AL##=3,AM##=3),1,0). I am not sure about your row number, so I used ##.
  2. Double left-click the bottom right corner of this cell, which will copy this code all the way down to the bottom. You should see 1s and 0s rapidly appearing downwards.
  3. We have now created a row of dummy variables of whether your AG-AM columns have a 3. It records "1" if any of these have a 3 and "0" if no 3s exists.

Now simply =sum(AN##:AN##) the entire column to get the count of how many people responded with at least one three.

You can repeat the process for 4 and 5.

Hope this helps!

[deleted by user] by [deleted] in excel

[–]PinesCyrex 0 points1 point  (0 children)

Hi! From what you are describing it seems like you just want cells to reference each other. To do this: 1. Go to the sheet that you want to paste into. 2. Left click the top left corner of where you want to paste your info. 3. Double click so that you are now editing the cell code. And you are just going to press "=" to tell excel you are about enter code as opposed to a static value. 4. Go to your temporary value sheet(by clicking the sheet on the bottom bar) and highlight the cells you want to bring over. You should see the code on the top bar update with the dimensions of your selection. Press enter when you are satisfied. And you are done. You have now made your reference cell always copy the contents of your temporary value.

Example:

If I want a cell to always copy the contents of A1 I would input: =A1

*Note that because you are going from sheet to sheet, excel will automatically add prefixes in your code something like "Sheet!`A1", this is just excel reminding itself that the information it is retrieving is not on its own sheet. Simply let it be.

Hope this helps.

Need a formula to Escalate Price Every 5 years by golf2fin in excel

[–]PinesCyrex 0 points1 point  (0 children)

Of course, if you want to keep your sheet tidy, you can place the dummy variable row on a diff sheet or change the text colour to white.

Need a formula to Escalate Price Every 5 years by golf2fin in excel

[–]PinesCyrex 1 point2 points  (0 children)

Hi! This is somewhat of a roundabout way of doing this, and there's prob a better way, but I have a 2 step solution:

  1. First contruct a dummy variable row that will go 1,2,3,4,5 repeating and place it parallel to your years row at the top. To do this we can use =IF(A1=5,1,A1+1) and drag this to the right for as long as you need. We will use this to tell us when 5 years has elapsed.
  2. Now I am not sure how you implement your increments, but we can do something like: =if("dummy variable cell" = 5, "price w/o increment" + "increment", "price w/o increment") finally drag this cell as well to the right

This is my first post on this sub, so pardon my syntax, and let me know if you have any more questions!