I want to add a different formula to an existing multiple if function

[krizcola]

I have multiple if formula that basically computes in a specific way depending on the values of a reference column. My reference column is "Data" (which is a list) and depending on the value selected, excel will either divide "target"vs"actual" or "actual"vs"target". My problem is, there is one item on the list "BFIR" which requires a totally different formula (sumifs most likely). Basically, what I want to do to this, if "BFIR" is selected in the "Data" list, and the value in column "Actual" is 1, excel will sum up (only) these values and divide the sum by 6.1. I want to add this to my existing multiple if formula. is this possible?

Here's the formula that I'm trying to use but is not working:


Thanking you in advance!

[Pete_UK]

It would help if you gave us the formula that you are using (working or not), or even attach a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do not try to use the Paperclip icon, as it does not work on this forum.

Hope this helps.

Pete

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[krizcola]

Thank you so much guys. I really want to add my formula and table to this thread but the site is not allowing me. I'm getting this error message: The following errors occurred with your submission
You are not allowed to post any kinds of links, images or videos until you post a few times.

How am I gonna get past this message?

[Pete_UK]

Just follow the directions that Richard and I gave you - you can attach files directly to one of your posts here (even with your first post), so there is no need to post any links or to use a 3rd party hosting site (you need to have at least 5 posts before you can post a link).

Hope this helps.

Pete

[krizcola]

Let's see if this works

I have the following columns: Month, Data, Target, Achieved, %Achieved (inside a table). I am calculating in the %Achieved column

[krizcola]

The formula kind of worked but as you can see, the computation is not updating. It's supposed to show 16.39 as a result in E4 but update the same to 32.79 in E5 but it did not do so.

[AliGW]

Attach the workbook, NOT a picture of it. Pete gave very clear directions about how to do this.

[krizcola]

Ok. Hope this works. I couldn't zip the entire workbook unfortunately.

[krizcola]

Sorry everyone but i'm having a hard time adding the workbook here because of the size. it's about 7MB on it's original size probably because of the macros, pivot tables & charts I have added. Even if I zip it, I still get an error message when I try to attach it. I was wondering if somebody can give me any suggestion on how I will be able to send it here. Thank you.

Cris

[AliGW]

Will you please attach a small sample Excel workbook?

Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

[Richard Buttrey]

Reply

You probabably want a COUNTIFS() function, probably

Please Login or Register  to view this content.

But as we've said, upload the workbook and manually add the results you expect so that we can understand the goal.

[krizcola]

Hello everyone. Since I cannot attach the workbook, I will try and describe my problem here. Here is a sample of my data.

July DPMO 1052 0
July BFIR 6.1 1
July Repair 168000 150133
July Inspection 494000 497737
July Near Miss 182 174
July Dock Time 50 48
August DPMO 1052 0
August BFIR 6.1 1
August Repair 133000 118621
August Inspection 494000 497737
August Near Miss 182 74
August Dock Time 50 52

These columns are in a table with headers: Month (column A), Data (column B), Target (column C), Achieved ,(column D) %Achieved (column E).

My point of reference is column B. I'm calculating in the %Achieved column where if the entries in Data are DPMO, Repair, Inspection, Near Miss, excel will execute the formula Achieved/Target . If the entry in Data is Dock Time, excel will execute the formula Target/Achieved. If the entry in Data is BFIR, excel will 1) correctly identify that the entry in column B is BFIR; 2) look up the entries in the Achieved column (D) and if there is a number there other than 0, it will automatically do a running total (of BFIR only); 3) divide the sum by 6.1. It will be kind of a running total so like in my sample data above, if row 2 is a BFIR and has a 1 entry, %Achieved column should show 16.39%. Since row 8 has another 1 entry, excel should add the 1 in row 2 and the 1 in row 8 and divide it by 6.1 showing the result as 32.79%. If another row should have ........ say 2, excel would add again so we will have 4 then divide the sum by 6.1 thereby giving the result 65.57% ...... and so on and so forth.

So far I have used the If Function and it worked for the others but not in the BFIR part since it's a bit more complicated than the others. I have tried using sumifs() as seen in my screen capture. Unfortunately, all it showed was 16.39% in all the rows with BFIR. Hopefully, I described my problem well.

Cris

[Richard Buttrey]

What is preventing you attaching your workbook or at least a representative cut down copy of it?

Do you mean you don't know how to upload it, or it contains sensitive data - in which case just anonymise the data. We don't mind how many M. Mouse or D. Duck names are featured - or indeed D. Trumps.

[krizcola]

Hi Richard,

I'm not sure either. I think it's the sheer size of it. It's about 7MB now because of macros, pivots, charts. The table in itself, the one I just outlined in my last post, is linked to that worksheet via macros in another sheet that automatically transfers data to the table. I tried WinZipping the file ......... that's the only zip available on my work laptop ....... but the result still came out a bit shy of 3MB and so the site won't let me upload it. I get the "Failed or Unable to Attach File" message.

I would love to attach the entire workbook. Nothing confidential there but the site won't let me. The table I outlined in my last post is representative of actual data I'm using. My pivots and charts are based on that table but the entries are not inputted directly there. I have another entry sheet that's connected to that sheet by a command button. It's not a big table yet as I only have entries for July and August and some made up data for September but I've already established the pivot and chart connections and am just stymied by this formula.

[AliGW]

We don't want to see the entire workbook. You just need to create a SMALL sample workbook that shows us your problem and a mock-up of what you wish to achieve.

[Richard Buttrey]

Macros, pivots and charts don't take up much room. I guess you have a table with some thousands of rows, maybe even a sheet where a formulae has been copied down to row 1048576.

Remove all but a few dozen rows and the workbook should shrink in size.

In the final analysis save it as a .xlsb file.The forum allows these up to 9.4 Mb

[krizcola]

I figured out why I can't attach my table, the table row went up to the end. I just reduced the rows. I am attaching the actual table now with the formula. Please and thank you in advance.

[Pete_UK]

After all that, I had forgotten what it is that you wanted to do !!

Change the formula in E2 to this:

=IF(Table9[[#This Row],[Data]]="Yearly Near Miss Reporting",Table9[[#This Row],[Achieved]]/Table9[[#This Row],[Target]],IF(Table9[[#This Row],[Data]]="Yearly DPMO",Table9[[#This Row],[Achieved]]/Table9[[#This Row],[Target]],IF(Table9[[#This Row],[Data]]="Yearly Repair Volume",Table9[[#This Row],[Achieved]]/Table9[[#This Row],[Target]],IF(Table9[[#This Row],[Data]]="Yearly Inspection Volume",Table9[[#This Row],[Achieved]]/Table9[[#This Row],[Target]],IF(Table9[[#This Row],[Data]]="Dock Time",Table9[[#This Row],[Target]]/Table9[[#This Row],[Achieved]],SUMIFS([Achieved],[Data],"BFIR",[Achieved],"=1")/6.1)))))

Changes shown in red - it only affects the SUMIFS term.

Hope this helps.

Pete

[krizcola]

Hi Pete,

Sorry for all the confusion and thank you so much for input. Somehow it's not working. Here's what I have in that row:

IF([@Data]="Yearly Near Miss Reporting",[@Achieved]/[@Target],IF([@Data]="Yearly DPMO",[@Achieved]/[@Target],IF([@Data]="Yearly Repair Volume",[@Achieved]/[@Target],IF([@Data]="Yearly Inspection Volume",[@Achieved]/[@Target],IF([@Data]="Dock Time",[@Target]/[@Achieved],SUMIFS([@Achieved],[@Data],"BFIR",[@Achieved],">0")/6.1)))))

I'm getting the answer 16.39% in all the rows with BFIR. I have already toggled between >0 and =1. Same results.

[Pete_UK]

When I used the formula that I gave you, I got 65.57% in all rows with BFIR in column B - the same value that you have in cell J1. It also shows up in rows where there is no data, as your formula does nothing to prevent this.

I don't recognise the @ symbol in the referencing, but maybe this is something introduced in a later version of Excel than the one that I'm using.

Pete

[krizcola]

Hi Pete,

The @ symbol is definitely a new addition to excel. it references the column name where the values are going to be derived from. I made 2 different variations to the formula, as follows:

IF([@Data]="BFIR",SUMIFS($D:$D,$B:$B,$B2,$D:$D,"=1")/6.1)))))) ----- I get the results 81.97% in all rows with BFIR.

SUMIFS(D3:D18,B3:B18,"BFIR",D3:D18,">0")/6.1))))) ------ I get inverted results in all rows with BFIR, row 4 is 81.97%, row 5 is 65.57%, row 14 is 49.18%, row 15 is 32.79% and finally row 17 is 16.39%. I don't understand why I got inverted results with this formula.

This is giving me a headache

[huuthang_bd]

Hi krizcola,

The formula return different results because of using relative reference.

[krizcola]

I got something to work:

SUMIFS($D$2:D2,$B$2:B2,"BFIR",$D$2:D2,"=1")/6.1)))))

Thanks everyone!

[AliGW]

You should be able to replace "=1" with just 1:

=SUMIFS($D$2:D2,$B$2:B2,"BFIR",$D$2:D2,1)/6.1)))))

This thread might be of interest to you: https://www.excelforum.com/excel-for...ing-range.html

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Pete_UK]

I'm not sure why you need that final term in the SUMIFS - you want to add column D if column B is "BFIR" and column D is 1, but if column D can only be 1 or zero then there is no need for it.

Hope this helps.

Pete