Divide by zero error when trying to get an average from cells containing IFS formula

Oct 26, 2020

Hi, Ron,

Your formula is outputting numbers as text and not numeric. You cannot perform math operations on numbers as text.

When you enclose a number in double-quotes you are telling it to be as text.

I duplicated the Status tab and deleted all your formulas in column B and replace it with a single formula in cell B3 only as follows

=ArrayFormula(IFS(A3:A=0,,A3:A<=25,4,A3:A<=100,3,A3:A<=250,2,A3:A>250,1))

I also duplcated the Input tab an deleted all the formulas in column J and replaced it with a single formula in cell F3 only as follows

=ArrayFormula(IF(LEN(I3:I),iferror(I3:I/F3:F),))

I hope this helps,

Ben Liebrand

Original Poster Rod Warren 5014 marked this as an answer

Helpful?

All Replies (23)

Oct 25, 2020

Hi, Ron,

Keep in mind we cannot see what you see so it would be difficult to offer a solution? There are many unknown variables so we would need something to work with.

For me, or other forum contributors to help (in case I'm not around), I suggest that you share an editable COPY of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do! Here are some tips to help you get a great answer quickly... Product Experts TIPS FOR SHARING

***** Tip: Read the Tips post above, then check your sharing settings before posting - you want "Anyone with the link can Edit"

Ben Liebrand

Oct 27, 2020

Hi, Ron,

Based on what you have shown, see my example tab in your spreadsheet.

I am using the following formula is row 3

={Ford!C1;Chev!C1}

={Ford!B1;Chev!B1}

={Ford!D1;Chev!D1}

I hope this helps,

Ben Liebrand

When you've received a response that answers your question, please observe these forum courtesies;

• Leave your demo sheet shared as part of this solution's archive,

• Click Recommend on the post that best addressed your question, and

• Post again soon!

Oct 27, 2020

You are the spreadsheet king! Hahaha. That's a really effective formula. Is there a method where that formula can be modified so that instead of typing the sheet name directly in the formula, it would reference Column A for the sheet name? Or to search a range in Column A such as from rows 3-100? If I understand the formula as it is now, if I were to add another sheet I would need to manually type in that sheet's name into each formula in columns B, D, and F (and in my actual sheet that I will be using there will be about 20-25 columns so that would be a lot of editing each time I add a new sheet). I have a different and unrelated sheet with a formula in it that seems to pull data from a source sheet by searching for the name, here is the formula:

=INDEX(cardata!$A$1:$Z, MATCH("ford",cardata!$A$1:$A,0), 4)

That formula looks to the 'cardata' sheet and searches 'ford' and then returns the value from the 4th column. I think. lol. I did not create the formula I just saw it in a different file and that's how it seems to work.

So my question summarized is: Is there a method to modify the formula you put in 'Ben_Example_Output summary' so that it will look in Column A and use whatever name is in Column A to pull data from any sheets that are the identical name as the word in Column A. In your example sheet, if I were to put in cell A5 the word 'Dodge' and also create a sheet called 'Dodge' then the formula would look for the sheet called 'Dodge' and pull in the data as is now in Columns B, D, and F (warranty length, price, colour options). The end result would be that I could create new sheets (like 'Ford' and 'Chev') and then all I would need to do is type the name of that sheet in Column A of 'Ben_Example_Output summary' and it would pull in the required data without needing to add the name of the sheet to the formula.

Please let me know if you need anything clarified in what I'm asking.

Thanks again Ben,

Rod

Click here to see my example in your spreadsheet.

Oct 27, 2020

Hi, Ron,

I am not sure I understand what you are asking? There is a lot going on in what you are describing which seems confusing.

Anyway, I took a wild shot at this and created Another_Example tab in your spreadsheet and entered the following formula into cell B3

=ArrayFormula(if(len(A3:A),iferror(vlookup(A3:A,{Ford!A3:D;Chev!A3:D},{4,2,3},)),))

As I said, it is a wild guess and I could be barking up the wrong tree.

I hope this helps,

Ben Liebrand

Oct 27, 2020

Hi, Ron

And yes, if you ad more tabs to your spreadsheet then you have to manually enter them into the formulas. Google Sheet will not do this automatically. I mean, how would Google Sheets know it ahs to add the new tabs. It has to be a manual decision from your side.

I hope that makes sense.

Ben Liebrand

Oct 27, 2020

The sheet you created 'Another_Example' certainly helps. As for what you asked about adding a new sheet, I was wondering if it would be possible to tell G Sheets to look in a specified column and take the name in that column as a sheet name for the formula. So as an example, in the formulas currently there is text such as: {Ford!A3:D} so I was wondering if instead of typing 'Ford' I could input something that tells the formula to look in a specific cell such as A3 where I would type 'Ford' and that way the formula would find the Ford sheet. Then I would type the names of 30-40 auto brands in column A and the formula would pull them in. That is what I was trying to say earlier. But if G sheets can not do it that way, it's OK. All the edits and ideas you helped me with over the last few days have been fantastic!

I noticed in the instructions that the site wants me to keep the shared version of this sheet available, which I can do if you would like. I'd assume that moving it to a different folder will not 'unshare' at all? I just have it in the root directory so I'd like to just tidy that up.

Thanks Ben

You can look at this example in your spreadsheet and see if you can do anything with this

Oct 27, 2020

Hi, Ron,

Before designing a project you need to understand what you can and cannot do. I don't understand your project so it is difficult to say do this or do that.

So, yes, you can enter Ford into a cell and use it in a formula. The problem is, it is very limited and inefficient. You would id the indirect() function. The problem is, the indirect() function cannot be used in an ArrrayFormula.

The way you design a project would be very different from the way I design a project. I like to simplify things and get the spreadsheet to do the work for me and not the other way around. That way I also reduce the number of unnecessarily complicated formulas as well as drawn-out development time.

When your spreadsheet becomes too complicated it is not efficeint to update amd maintain from formulas point of view.

So by using the indicrect() function you would end up with many formulas that needs to me updated in many placed in the spreadsheet.

Of cource, if you are doing something very simple and basic then you can opt for doing thing that way. But as I said, I don't understand your project and you have not shown an example of what you are attemptiong to do. When you descibe it to me I have to try and visualise it and that makes it difficult to grasp it and offer a solution.

The formula I use is as follows

=iferror({indirect(A3&"!A3:D")})

I hope this helps,

Ben Lierband

Last edited Oct 27, 2020

Oct 27, 2020

Hi, Ron,

I would recommend you familiarise yourself with the various functions individually using simple and basic data sets so you can understand how they work and behave. That will give you better direction on how to approach your project.

Google Sheets is very powerful and you can do great things with it. But you want to take the best approach that will be more efficient and simpler to maintain.

I hope this makes sense.

Feel free to ask questions, but try and offer an example of what you are trying to achieve.

Ben Liebrand

Oct 28, 2020

Hello Ben, though my original inquiry has been answered as you know several other very helpful items came up via your responses, and as per your last two messages to me, I have added 3 new sheets that can serve as an example of what I'm trying to achieve. They are the ones labeled Auto Summary, Auto BMW, Auto Toyota. The summary sheet would pull in information from each Brand's sheet, as well as have columns for inputting information that is not in the Brand's sheet as well as calculating some outputs / rankings from 1-4. I did not actually fill in the formulas since it is just an example, so I put in the cells in the Auto Summary sheet a description of what the formula would achieve; as far as I'm concerned there is no need to input the formula since they are similar to the ones we've already arranged in other sheets. So the remaining issue which these example sheets are for, is to figure out how to pull in data from each 'Brands' sheet. I have 2 brands there in the example, so if I were to create a Brand sheet for another 40 different auto manufacturers, is there a method to have a formula in the Auto Summary sheet that will pull it in without needing to manually type the Brand name in all the formulas for its row? I have the logic for the formula in my mind but I don't know how to write it. It would look in Column A (Brand) and use the name in that column to reference a sheet by the same name, so all I would have to do if I added a third Brand is: 1) Create the sheet and name it 'Ford' and then in the Brand column of 'Auto Summary' type in 'Ford' in cell A4 and then in row 4 all the appropriate data would get pulled in. The formula that I saw months back in an unrelated sheet is the one that made me think such programming is likely possible; here it is again:

=INDEX(cardata!$A$1:$Z, MATCH("Ford",cardata!$A$1:$A,0), 4)

This formula tells G sheets to search in a certain range for a word "Ford" and then pull data from that sheet, from specific cells.

That type of formula would not work because I'd still have to type 'Ford' in it, and would have to do the same for every formula in the row, so I figure there must be a method to tell the formula to reference the text in Column A instead of having to type the Brand name (sheet name) in the formula.

So ultimately I would have a spreadsheet that will automatically pull in a variety of data from a new sheet simply by putting the name of the sheet in Column A and having a formula get the sheet name from Column A.

I hope the example I set up and this explanation clarifies what I'm trying to achieve in this project.

Thanks again Ben,

Rod

Oct 29, 2020

Hi, Ron,

Typical formula examples

=if(len(A2),indirect("Auto "&A2&"!B1"),)

=if(len(A2), IFS(C2<21,4,C2<26,3,C2<36,2,TRUE,1),)

=IF(LEN(A2), IFS(F2<100,1,F2<200,2,F2<300,3,TRUE,4),)

In each case the formulas are dragged down to the last row in the tab.

Click here to see my example in your spreadsheet

I hope this helps,

Ben Liebrand

Oct 30, 2020

Hello Ben,

That formula is ingenious! It did exactly what I was hoping for. Big thanks to you for all your help on this journey. I will keep the spreadsheet shared as per instructions. If I have other spreadsheet questions in the future is there a way I can contact you instead of doing a general post and hoping for the best?