excel formula

Wood · 2019-07-21 22:59:37

i need excel help.a1:a6 base values of b1:b6.In c1:c3 search values500,400,300.a1 is 300,b1 is 285,a2 is 400,b2 is 350,a3 is 300,b3 is 290,a4 is 500,b4 is 467,a5 is 300,b5 is 285,a6 is 400,b6 is 320.I need display in d1 is 467,like d2 is 350,320.in same cell,d3 ans 290,285.That is number value one time in single cell with commos with descending order.kindly help me sir.

Bob · 2019-07-22 20:08:45

hi Wood,

It looks like you want the 'd' column to have the 'b' values but in descending order of size, and grouped so that all the numbers in the d3 cell, for example, are in the 200-300 range in descending order across the cell, with commas between.

That's quite a challenge as Excel only allows one number per cell. You can convert numbers to text with the command =TEXT(1234,"##0"). The function needs two inputs; the first is the value as a number; the second is the format you want. I've assumed that all your data are whole numbers,

Then you'd have to add text together with the command =CONCATENATE(text1, " , ", [text2], ...). Note that the insert " , " puts in the commas for you.

First use the SORT function to re-order the B column. I cannot test this step because the function doesn't work in my version of Excel.

Then you've got to find the values in each range. I think the simplest way to do this would be with =IF.

eg =IF(AND(B1>200,B1<300),B1,) This will pick out all the numbers in that range and place them in that column. You'll have to do this for each range.

So you'd need a lot of columns just to separate out the ranges, then convert the results to text, then concatenate the text with "," between to insert the commas.

Wouldn't you rather use some computer code for this

Bob

Wood · 2019-07-23 05:15:02

Sir, first of thank you for your nice help. I simply explain a1 is 2, a2 is 7,a3 is 1, a4 is 3,a5 is 1.b1 is search value is 5 less or equal to, then return all numbers, descending order, in one cell d1 itself single comma separated cell . Answer in d1 3,2,1.

Wood · 2019-07-23 05:17:52

Array formula need in excel

Bob · 2019-07-23 18:56:59

hi Wood

I've looked up array formulas in Excel help, and I don't think this will do what you want.

Please would you check if you can sort a column into order (descending).

eg if B1 = 3; B2 = 5; B3 = 1; B4 = 7; B5 = 2.

Try the formula SORT. Can you get:

C1 = 7; C2 = 5; C3 = 3; C4 = 2; C5 = 1

My version of Excel is too old and doesn't have this formula.

Bob

Wood · 2019-07-23 23:36:43

Need less or equal to 5.answer must be single cell in d1 itself 5,3,2,1.

Wood · 2019-07-23 23:38:33

All answers in single comma separated cell in d1 with descending order, without repeat numbers in excel. What formula need that

Bob · 2019-07-24 03:03:04

Yes I understand that. I do not think there is one formula which will do this. So we must do it in steps.

Step 1. Sort the B column into order. Are you able to do that?

Then this:

Bob

phrontister · 2019-07-24 10:38:31

Hi Bob & Wood,

Here's an image of my attempt, with formulas displayed in the lower section. I used some different values in column A than in your posts, just for testing purposes.

B1 contains the search value (5, in this case), which with my spreadsheet meant using an extra column (E). However, it enables changing the search value (if required) easily.

Column C returns all values <= the value in B1 ('5').

Column D gives the list of all unique Column C values (excluding blanks), in descending order.

The single cell E1 contains the comma-delimited output (in descending order) of the range D1:D4. Unfortunately I couldn't think of an automatic way to use TEXTJOIN that doesn't require having to select the cell range.

============================================================================================

Hi Bob,

With my version of Excel, 'CONCATENATE' joins numbers and returns the result as a text string, so there's no need for column C's conversion to text.

You may already know this: CONCATENATE has an alternative 'ampersand operator' option, used like so:
=C1&","&C2&","&C3&","&C4&","&C5

Wood · 2019-07-24 18:02:04

Phrontister Sir,
Thank u. Without concatenate, textjoin, only use array formula index match large to get answers in one cell. I need help... First post.

phrontister · 2019-07-24 18:09:29

Hi Wood,

Could you please show us the best formulas you have tried.

Thanks.

Wood · 2019-07-24 18:09:42

Phrontister,
Kindly look my first post. Index, match large used without cse first match come. Then we press cse,first match number it's shows all numbers in single comma separated in one cell without helper column.

phrontister · 2019-07-24 18:44:42

I'm sorry, Wood, but I know nearly nothing about arrays and {INDEX,MATCH,LARGE} formulas, and unfortunately I don't have enough time to research and learn this area.

I hope someone else here can help you (and maybe Bob knows how), but if not it may be best for you to try an Excel forum.

Wood · 2019-07-24 22:53:51

Bob, any idea sir

Bob · 2019-07-25 03:21:10

hi

I've not met the function LARGE before. That was useful as it gives me a way to sort the B column. I do not understand how INDEX and MATCH will help.

You cannot get more than one number into cell D1 so the only way I can think to do this is to convert the numbers into text and then join the text together. Here is my sheet with the actual cell contents at the top, and then the formulas I used below. I have annotated it so you can follow what I did.

Bob

phrontister · 2019-08-14 09:08:35

Hi;

I spent a bit of time trying find a solution that used the OP's requirements, but I failed and went back to what I had tried before with TEXTJOIN because I felt I'd got close then (sorry, Wood).

This time I think I succeeded (see image). The method returns column D results that are distinct, comma-delimited, and with each cell's contents sorted into descending order...as per post #1's requirements.

Some comments:
1. I used Excel's 'Sort Z to A' feature to sort each column D cell into descending order (don't know how to do it via formula).
2. I haven't met post #10's requirements (don't know how).
3. I populated column C via LARGE, with distinct values in descending order.

Last edited by phrontister (2019-08-25 11:57:01)

MarySunn · 2022-02-27 10:40:30

Hey guys. I have a problem here. Can anyone help me figure out the charts? And if you know, please advise a program in which it is most convenient to deal with graphs and charts. I'm currently using the services of the guys from http://www.wallstreetoasis.com/resource … -of-graphs. But I would really like to learn how to do it myself. I even rather need this skill. My job involves constantly presenting information in graphs and charts. I also heard that a powerful computer is needed for this kind of work. Is it true? If so, it's worth buying a new computer, too. Thank you in advance to all who respond.

Jai Ganesh · 2022-02-27 13:12:56

Hi MarySunn,

Welcome to the forum!

Please see the links below:

In my opinion, buying an advanced computer certainly helps. Please check the prices and utilities.

Good Wishes!

18 Best List of latest Technologies in Computer Science:

Computer science has been involved in many sectors of our life. We are using technology in every sphere of our daily work. Discussed below the latest technologies in computer science.

1. 5G Network

The 5G network brings revolutionary changes in the whole world. It is most helpful for the fastest service in the modern sector. The rise of 5G networks is expanding our capacity to move, control, and analyze information over wireless stages, agreeing to CompTIA.

As 5G rolls out more completely within the coming a long time, it’ll drive the improvement of more complex apps to solve issues and increment development over industries. Arrangement suppliers that create 5G-based arrangements for particular industry applications will have beneficial, early-mover advantages.

2. Artificial Intelligence and Machine Learning (AL & ML)

Fake intelligence has had an incredible effect among the best innovative improvements and for very some a long time, as well. Not surprisingly, it has moreover found a put within the estimates of the 2020 technology.

AI applies to computer frameworks that are modified to imitate human insights and perform assignments such as picture acknowledgement, discourse or designs, and choice making. AI can perform these assignments more rapidly and precisely than a human.

ML and AI will offer organizations phenomenal knowledge into their competitive scene, current execution, and allocation of resources. And, in impact, marketers can utilize these experiences to significantly improve performance, at the side of numerous others.

Microsoft reported that early adopters of AI for commerce within the United together Kingdom have as of now seen a 5 percent increment in productivity, quality and commerce come about relative to those that have not investigated their expanding capability scope. In 2020, marketers ought to be obsessed with AI and ML for these reasons.

3. Automation

Considering that various developing advances such as cloud computing, enormous information, fake insights, and mechanical technology are heading for high-scale development, it isn’t astounding to see that robotization is at its tallness. Numerous program counselling firms oversee computerization arrangement right from managing an account to fabricating and computer program companies.

4. Block chain

Blockchain is the best developing innovation of tomorrow. It could be a decentralized computerized record that stores exchanges on thousands of computers around the globe. They are enrolled in a way that hinders their consequent alteration.

Blockchain innovation increments security and speeds up the exchange of data in a way that’s cost-effective and more straightforward. It also apportions with third parties whose primary part was to supply a component of belief and certification in exchanges.

Huge enterprises are primarily looking at three Blockchain stages to develop top-level trade solutions. The request for engineers of blockchain is on the rise. You’ll be working with the Bitcoin and Ethereal conventions in this course, making ventures for real-world applications, and learning the fundamental abilities in this energetic space for a career.

5. Cyber Security

Cyber Security Think about Programs instruct you how to ensure computer working frameworks, systems, and information from cyber-attacks. Learn how to track gadgets as they emerge and diminish dangers.

The general objective of learning cybersecurity is to assist you to create the specialized abilities vital to anticipate assaults and secure the information and privacy of individuals.

Living within the computerized age implies there are unending openings for programmers and cyber terrorists to target individuals, government teaching, and indeed huge trade.

Best organizations are willing to pay a parcel for cyber investigators who can ensure their information and evacuate vulnerabilities to guard against cyber assaults and security breaches.

As businesses and governments alike are going advanced, cybersecurity may be a fast-growing and unavoidable requirement. The number of cybersecurity labourers is rising three times quicker than other tech employments as proof of the solid requirement for cybersecurity experts.

6. Voice Technology

Within the past, we’ve seen progress over voice innovation like Sire, Alexa, and others, but they’ve continuously fallen short of human measures – or even become useful in way of life. Voice could be a. characteristic and free-flowing medium, something that’s not effortlessly translated into computerized innovation.

Voice commands and voice associates will be indeed more valuable in our day by day lives within the close future, muddling the line between the interfacing between human advances.

As the fundamental innovation of these segments propels (AI, discourse acknowledgment, machine learning), it’ll as it was pushed into more prominent specialized significance. NLP innovations will be the innovation that brings voice innovation to its full mechanical value through the mysterious value.

7. Virtual Reality

Virtual Reality (VR) progresses the user’s experience while Increase Reality (AR) improves the environment. There are major players on the VR market, such as Google, Samsung, and Oculus, but there are a bounty of start-ups rising and enlisting, and the request for VR and AR abilities experts will as it develops.

It does not require a part of specialized information to urge beginning in VR. Essential programming abilities and forward-thinking attitude can make work, although other bosses will moreover explore for optics as an ability set and equipment engineers.

8. LOT

LOT is driving trade changes by giving the information required to move forward marketing, increment deals, and diminish costs, the report found.

“Everybody within the innovation world, as well as numerous buyers, is hearing the term Internet of Things,” Straight to the point Raimondi, a part of the CompTIA Developing Innovation Community administration bunch who works in the key channel and commerce improvement for Chargaff, said in a press release.

9. Artificial Intelligence

AI is as of now altogether affecting the way clients are associated with businesses by means of shrewd websites and bots, and these instruments are getting to be progressively commoditized and coordinated into everyday work, the report noted.

The impacts into industries—from retail to healthcare, neighborliness to finance—are felt when AI strides information security, decision-making speed and exactness, and worker yield and preparing Community bad habit chairs and head of development and instruction for Smith.

10. Serverless Computing

Serverless computing permits organizations to form a Snoops IT environment that’s mechanized and disconnected from the fundamental foundation, lessening operational costs and permitting businesses to contribute creating modern capabilities that include more esteem, the report found. Serverless computing was modern on the list this year, together with mechanical autonomy, supplanted quantum computing, and computerization, Comitia famous.

11. Biometrics

Biometrics- including confront, fingerprint, and retina scans-are getting to be standard strategies for confirming character. These strategies will shape the security establishment for solutions conveyed by IT companies moving forward, comitia said.

12. Robotics

Robotics is a mentionable invention of modern science. It makes the process of business faster, less expensive, and more efficient.

13. Virtual reality (VR/Augmented reality (AR)

Utilizing VR, AR mixed reality, AI, and sensor advances can offer assistance organizations progress operational effectiveness and person efficiency, according to the report.

14. Drones

Drones empower robotic computerization with less geological restrictions, the report noted. Opening for advancement and integration are tall for this advertise, it included

15. Intelligent Apps

Another era of mobile applications will coordinate numerous advances and will be a strong blend when application development meets artificial intelligence, the internet of things, and enormous information analytics.

Put basically, these are apps that continually learn from user interactions and other information sources to end up indeed more important and valuable. Based on Wi-Fi and smartphone information, a smart app can determine when to turn off the lights in an office building and when to put the machines off in a production line.

16. NFT

Right now NFT is a trading topic around the world. So, what does it mean by the NFT- A Non-Fungible Token). This is a sort of crypto which records the main owner of the digital wallet or items. It has become the most valuable asset in the respected creative world. Through the NFT any form of artwork, music, internet memes, and tweets selling in million dollars.

To buy the artwork anyone can see who is the real owner of that particular digital work. Recently, someone bought Elon Musk’s first tweet. It is said that NFT will be the big invention of the digital world.

17. Big Data analytics

Big data is a complex process of examining big data to find out the needed information such as – market trends, correlations, various patterns which success in the field, and customer behavior as well. By analyzing those data come out the informed business decisions.

17. Machine Learning

Machine learning ( ML) is a process of analyzing data which is building an automatic model. It is the inner part of artificial intelligence from which the system can understand patterns, data, human intention, making decisions and predictions to make decisions without taking any help from other programmers.

18. Computer Network

A computer network is two or multiple computers interconnected with the common communication protocol in the digital medium. Those computers share their information with one another. To build this type of equipment requires a lot of equipment. To set up those computers need a physical area to see if there is any need to use for business purposes. A good example can: hosting services.

Conclusion

Technology in computer science made the system of work easy and short. Above, List of latest technologies in computer science is a blessing of modern science. We can do anything within a short time by the use of this technology.

Math Is Fun Forum

#1 2019-07-21 22:59:37

excel formula

#2 2019-07-22 20:08:45

Re: excel formula

#3 2019-07-23 05:15:02

Re: excel formula

#4 2019-07-23 05:17:52

Re: excel formula

#5 2019-07-23 18:56:59

Re: excel formula

#6 2019-07-23 23:36:43

Re: excel formula

#7 2019-07-23 23:38:33

Re: excel formula

#8 2019-07-24 03:03:04

Re: excel formula

#9 2019-07-24 10:38:31

Re: excel formula

#10 2019-07-24 18:02:04

Re: excel formula

#11 2019-07-24 18:09:29

Re: excel formula

#12 2019-07-24 18:09:42

Re: excel formula

#13 2019-07-24 18:44:42

Re: excel formula

#14 2019-07-24 22:53:51

Re: excel formula

#15 2019-07-25 03:21:10

Re: excel formula

#16 2019-08-14 09:08:35

Re: excel formula

#17 2022-02-27 10:40:30

Re: excel formula

#18 2022-02-27 13:12:56

Re: excel formula

Board footer