How do I set the formula to calculate “If x falls between 1-10, return 1-10, if it falls between 11-20, return 11-20, and so on”

like
Posting as :
works at
You are currently posting as works at

=(ROUNDUP(A1/10,0)*10)-9&"-"&ROUNDUP(A1/10,0)*10

likehelpful

=IFS(AND(A1>=1,A1<=10),”1-10”,AND(A1>=11,A1<=20),”11-20”... and so on for however many groups you need

likehelpful

Yep this is how I'd do it

Create a Table with starting value and ending value (make sure that it is in ascending order) with a result column. So Start value, end value, category. Then use the LOOKUP formula

like

This is the easiest way, if there are updates to ranges or outcomes then just update the table... its the vlookup on the table with TRUE ending

like

(Int( (n-1) / 10)*10 + 1) & "-" & (int( (n-1) / 10)*10 + 10)

likehelpful

Pwc1 and private equity associate 1 got it

like

Google it

like

You must have not read what this group is for. Please go google that

IFS(condition 1, output, condition 2, output)

like

=FLOOR(A1,10)+1&"-"& CEILING (A1,10)

like

What if I don’t want increments of 10. Let’s say first gap is 250, second 500, this 50. Would this still work

VBA?

Related Posts

Genuine question: what’s the hype with Sinola watches? I feel like from the price point there are far better options in terms of style and horology

like

I’ll be asking my last job for a letter of recommendation but I’m not sure who to ask. My boss, who is lovely and would write an amazing LOR. Or my boss's boss who would put minimal effort in but it would have more weight.

like

Accenture strategy folks.. can someone confirm if work from home is about to end?

Hi fishes,

Comment your total hike, VP , ctc current, level, designation, tech stack you have received as per Nov 22.

like

Can anybody help me with Barclays leave policy in India....no of leaves...

I cleared technical round and during final call with HR, my salary expectations were higher as per HR.

My current CTC is 9lpa, skill-IAM and Java, YOE-3. I asked for 14lpa, best they could give was 11lpa. I know I asked a lot.

She didn't care to negotiate and cut my call and now they put my candidature on hold. Can I expect call back? Or i can consider myself rejected?

like

Hi guys,
I have cleared all the rounds for LTI post which I received autogenerated mail and then later HR asked to upload documents. In how many days can I expect offer letter?

like

Can someone refer me to Harman Connected Services ? I have 4 years of experience as a UI developer (React)
Thanks in advance.

like

Assurance Professional of the Future Program for Senior 1s = Milestone event??

like

Does anyone use MacBook for work? Does it work well with excel?

like

Bonjour ici !
Je suis un consultant italien avec nationalité française, j'ai été récemment contacté par une MBB et j'envisage de m'installer à Paris depuis le Moyen-Orient où je travaille actuellement.
Y a-t-il quelqu'un qui aimerait entrer en contact et me parler de son expérience?
Merci beaucoup !

like

What are the best consulting firms operating out of London known for / working on sustainability and energy transition topics? Many thanks!

like

When are we supposed to receive the official GSAP acceptance email?

like

If anyone is Hiring in Canada please kindly consider my partner. He is very hardworking, polite, diligent person and excellent team player.

-He got around 5 years of project management, bid management and proposal management experience for civil engineering consulting firm,
-He got experience in Primavera P6, got excellent analysis skill, can use python for constructing ML models,
- He is proficient in RFP,RFI,RFQ for construction projects as he got BSc in Civil Engineering and MBA.🙏🙏

like

What are the key differences in an account management role between PR firms and traditional ad agencies? Are there any major skills someone would need to build when transitioning to ad land? Or is a lot of it transferable?

like

Is it possible to go from Finance Compliance to Biglaw? I have 5y Biglaw experience abroad and NY license. Moved to NY for inhouse job in finance but considering moving back after 4y.

like

What is a decent annual billable requirement? Also, billing tips are appreciated. For some reason I am really struggling. Am I self sabotaging?!

like

News is in - Greenberg Traurig Miami Associates are matching NY Scale. Discretionary bonuses, with an eye toward market expectations. Who’s moving to south beach?

likefunny

Is working for a company that sells haram (Bank financial products, Walmart liquor products, etc) haram? Looking at an exit opportunity and part of (<10%) of their products are alcohol as a B2B seller. I want baraka in my earnings and want some help understanding this. Jazakallah

like

Additional Posts in Excel Genius

How do I look up values from a column in a row? E.g. Values in A1:A6 in B2:F2?

like

How do I compare two sheets to make sure they are identical?

like

Anyone know what these badges are?

Post Photo
likefunny

I’m verrrrrry new to excel and am going crazy because is there not a way to “clip” text in cells like you can in google sheets? I read about a “fill” option, but then noticed that cells that aren’t filled just repeat text. 😂🤯

like

Still don't have xlookup. #fomo

like

What are your top excel functions you use in finance / accounting - besides vlookup - Pivots - sumifs - count - index. I have an interview coming up which will have excel based technical questions. Not sure what other functions are considered important in the accounting finance world.

like

I am trying to recreate this without the numbers at the end. Any advice?

Post Photo
likehelpful

Struggling with this:
Suppose I want to shortlist companies that serve the priority industry vertical of a country. So I have the industries served by company (one column for each industry) and (cont)

Current IE here. What are some Time Motion Study Softwares that are out there and you use in your jobs?

like

Does anyone from PwC have a list of the favorite groups shortcuts for excel? Please provide. Much appreciated .

like

Anyone else have a formatting pet peeve and get annoyed when people don’t have a blank first column and row in tabs? I.e headers/data in cell A1

My first manager told me this, and I’ve followed it to a T since.

likesmart

Is there a way for me to automate creating columns to do this so I can drag across and create my columns? For example--
1Q2021 2Q2021 3Q2021 4Q2021 2021 1Q2022?

likesmart

I created a macro to help me save time on this onetime task and now whenever I open my excel, a blank excel opens with the name of the macro. How do I get rid of it?

like

What are some common reasons that a COUNTIF formula might not be working correctly? I'm using the correct range and value to count. I'm not seeing "ERROR" but the values aren't correct (it's not counting everything). Thx.

like

Anyone use power query before? My goal is I have multiple look up values in one cell (separated by comma) and I need it to look up those values and return values from another table which also may have multiple values (separated by comma) anyone know how to do that? I played around with power query today but it wasn’t giving me what I wanted

like

Hi Group, seems like it should be basic format, but I just can not get it to work. a list of data and want to alternate shade (blue,white,blue,etc) each line but if I have multiple lines I want to group together and they all shade the same. I want to group by the same data in column A, and the number of rows that are the same could vary. So let’s say row 1& 2 same entry in column A(blue) row 3 different (white) row 4,5,6 same as each other different to other rows (blue) etc… help 🙏

like

I’m helping my GF with excel and need help lol. She works at a hospital and is trying to make an excel schedule for doctors to reserve rooms. She wants drop downs for days of the week (column K) and also different drop down menus in column P for the reserved time slots.

Is it possible to do this with two sets of drop downs (days of week + time slots) and not use index match here? She could make 5 tables for each day of the week, but prefers one table that can be easily navigated by doctors.

Post Photo
like

Any book recommendations on excel modeling for real world business applications?

like

Has anyone had an issue with index match and lookups returning NA when trying to manipulate data from a password protected workbook?

Would anyone be willing to teach me vlookup?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal