Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel formula problems

Posted on 2014-07-28
14
Medium Priority
?
162 Views
Last Modified: 2014-07-30
Im getting errors with some formulas I have written.
Could someone help resolve.
0
Comment
Question by:gh_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 40225870
Did you miss an attachment?
0
 

Author Comment

by:gh_user
ID: 40225883
Thanks.
Its attached now
Excel-Formulas-Issues.xlsx
0
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 40225912
Okay, I've made the corrections in row 29 for you. (Max. risk for asset.) Unfortunately, I'm suffering from flu and need sleep and the other formulas are a bit too complicated for my brain to tackle at the moment! :-) Hopefully in the morning, if someone else hasn't already done those I can help you out with the rest!

If my Max. risk for asset formulas work out for you, I'd appreciate partial credit for those even if someone else jumps in and does the whole thing for you.. I'm short a few points to keep my qualified status for the month!

Here's the spreadsheet back at ya..
Excel-Formulas-Issues.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gh_user
ID: 40225972
Hi Miss Sellaneous.  Sorry your not well. The copy of the file you attached didnt have any change to the row 29 formula.
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40226021
To elimincate error in the formula use =IFERROR(yourformula,"")

To get the max risk, try this one.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"high","medium","low"},B$23:B$28))),IF(COUNTIF(B$23:B$28,"high"),"High",IF(COUNTIF(B$23:B$28,"medium"),"Medium","Low")),"")

I'm sure there may be an easier one.

Kris
0
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 40226581
AARGGGHHHH!! I just saw the one I had made changes to listed in my recent documents, with a time after 9pm, but then I downloaded the one I had uploaded to you last night to check it, and it overwrote the one with changes, and now it is back to the original one with a time about 2 hours earlier!

I will get on this ASAP and CHANGE THE FILE NAME before uploading it again!!

Sorry..
0
 
LVL 12

Assisted Solution

by:Missus Miss_Sellaneus
Missus Miss_Sellaneus earned 600 total points
ID: 40226633
Okay, here's the new version with the Max Risk For Asset, row 29 working. I swear.


I'll try to help you on the other functions as well..  need to familiarize myself with the syntax.
Excel-Formulas-Issues---With-Changes.xls
0
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 40226789
Okay, I have started by analysing cell C24 on the Input & Results sheet. After I removed the redundant referenece to the current worksheet to improve readability, I noticed that you are trying to MATCH on C5&C6 (concatenated). C5&C6 calculates to "LowAlmost certain", and since there is no match to the string "LowAlmost certain" anywhere in the entire worksheet I think that's the reason you're getting an error.

Perhaps it would be easier if you  tell us in more detail what you are trying to lookup using what values.
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 1400 total points
ID: 40227009
HI,

a shorter formula (Array formula => Ctrl-Shift-Enter) to enter it

=INDEX({"Low";"Medium";"High"},MAX(MATCH(B23:B28,{"Low";"Medium";"High"},0)))

and the errors are due to Moderate with space in the sheet Input and Results without space in the General sheet

corrected version

Regards
Excel-Formulas-IssuesV2.xls.xlsx
0
 

Author Comment

by:gh_user
ID: 40228458
Thanks Rgonzo1971 (and others)
All fixed.  New formulas suggested worked for Max Risk. Do you know why my use of IF function didnt work to find Max Risk.  If Im doing something wrong would be good to know, as often need to use IF statement
0
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1400 total points
ID: 40228473
Becaue you compare a range to a single value without array logic

without array formula¨

you could have done it with  ( a longer formula )

=IF(IFERROR(MATCH("High",B23:B28,0),FALSE),"High",IF(IFERROR(MATCH("Medium",B23:B28,0),FALSE),"Medium","Low"))
Regards
0
 

Author Closing Comment

by:gh_user
ID: 40228494
Lots of good responses.  Thanks
Have tried to split points in consideration of completeness and timing of response and simplicity of solution
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40228519
The accepted as well as the assisted solution gives error and Low respectively, in case if there is no match found. And you simply ignore my solution - the first one !
0
 

Author Comment

by:gh_user
ID: 40228553
Hi krishnakrkc
In my case there would always be a match.
Yes, yours was the first correct one but the formula was a tad long. So I didnt end up using it.
Its hard to score when muliple answers come in that address my queries.
Thanks for your efforts.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question