LinkBack URL
About LinkBacks
MOC Spreadsheet.xlsx
Please help with adjusting formula. Instructions in Excel File. Thank you!
MOC Spreadsheet.xlsx
Please help with adjusting formula. Instructions in Excel File. Thank you!
Welcome to this forum.
If you want to recognize which values are italicized, you need a VBA-solution, because Excel-formulas couldn't recognize which numbers are italicized.
Do you want a VBA-solution or is it possible to indicate in a other way which numbers should be excluded?
Last edited by HansDouwe; 08-07-2022 at 03:33 PM.
Hi! Thanks for replying. The only indication to avoid are the values being italicized. I am leaving an internship soon and my other employees have no VBA knowledge so I was trying to accomplish this without it. They will have to add data into a sheet of similar format and the formulas will most likely have to be edited because of this. I don't want the program to stop operating as soon as they do this because I will not be around to fix it.
I have also tried naming the data set excluding the empty and italicized cells and adding that to the formula instead but it gives back a value error. I am not sure if I am trying to do something that really isn't possible without VBA or if there is a better way to do this.
If you can only indicate with itlicized numbers which numbers should be excluded, then VBA is the only option, because ordinary Excel formulas cannot recognize the format of a cell.
If you want that, I'm sure a VBA specialist on this forum can handle this. (I'm not good enough either in VBA)
Or would it be an option to indicate with a flag next to each digit whether it should be excluded or not.
The flag can then make the number itilicized with conditional formatting.
The Excel-formulas can use the flag to exclude these numbers from the calculation.
There's nothing in the post or workbook that describes why those particular numbers are italicized. So, what rule are you using?
4.5 & 5.5 are next to empty cells - italics
8.5 next to empty cell - no italics
3.2 not next to empty cell - italics
Last edited by protonLeah; 08-07-2022 at 04:48 PM.
Ben Van Johnson
This is just a small worksheet example I made to mimic the workbook I am unable to share. The numbers, blanks, and italics are all random in the MOC Spreadsheet. My actual workbook contains italicized values that are unverified values and these cannot be used in our calculations, but they need to stay in the cell and location they are in.
Is there a way to make a formula exclude values attached to conditionally formatted cells?
Last edited by helpmeexcelexperts; 08-07-2022 at 05:26 PM.
I can only solve it without VBA if you want to indicate in a different way which numbers have been verified.
For example with this formula for the closest match in this sheet:You should mark which numbers are verified (included) or not verified (excluded).Formula:
=INDEX(I3:I10;MATCH(MIN(99999999*((J3:J10="exclude")+ISBLANK(I3:I10))+ABS(I3:I10-I13));ABS(I3:I10-I13);0))
This is possible, but there are many other possibilities. Maybe something like that could be an alternative solution?
If you can only see which numbers are verified by the font then VBA is involved.
Last edited by HansDouwe; 08-07-2022 at 07:06 PM.
Thanks! I will try to see if I can make something similar work! I really appreciate your time and help!
Glad to hear you appreciate this help. I liked doing that.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
If you have more questions or comments, please get back to us.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks