Re: Reduce / efficiency formula
Thank You snb.
Re: Reduce / efficiency formula
Thank You snb.
Re: Reduce / efficiency formula
Thank you AAE.
Re: Reduce / efficiency formula
Thanks for reply,
I dont want to change "if" with vlookup. I want to make more flexible formula if is possibil.
Hello,
Because I have many formulas in a spreadsheet trying to reduce their number.
How can I reduce / efficiency this formula:
=IF(AND(B4>=1900;B4<2000);AB20;IF(AND($B$4>=2000;$B$4<2100);AC20;IF(AND(B4>=2100;B4<2200);AC20+1;IF(AND(B4>=2200;B4<2300);AC20+2;IF(AND(B4>=2300;B4<10000);AC20+3)))))
Re: Index & Match formula for a number in a string
Quote from weebilko;657091Display MoreSorry to bother you again but your formula in G5 works perfectly:-
IF(G5="","",IFERROR(IF(LEFT(G5)="*","*","")&INDEX($L$5:$L$8,MATCH(LEFT(SUBSTITUTE(G5,"*",""),FIND("v",SUBSTITUTE( G5 ,"*",""))-1)+0,$K$5:$K$8,0)),INDEX($L$5:$L$8,MATCH(MID(SUBSTITUTE($G5,"*",""),FIND("v",SUBSTITUTE(G5,"*",""))+1,2)+0,$K$5:$K$8,0))&IF(RIGHT(G5)="*","*","")))
but when I copy down to G6 and G7 like so, only changing the G5 to G6 & G7 etc:-
IF(G6="","",IFERROR(IF(LEFT(G6)="*","*","")&INDEX($L$5:$L$8,MATCH(LEFT(SUBSTITUTE(G6,"*",""),FIND("v", SUBSTITUTE( G5 ,"*",""))- 1)+0,$K$5:$K$8,0)),INDEX($L$5:$L$8,MATCH(MID(SUBSTITUTE($G6,"*",""),FIND("v",SUBSTITUTE(G6,"*",""))+1,2)+0,$K$5:$K$8,0))&IF(RIGHT(G6)="*","*","")))
It doesn’t work and gives the value error, what am I doing wrong? Regards
Hope this G5 (bold) is a typo.
Re: When I enter a letter, the function does not work
Hi Rai77,
You try to add 2 apples with 5 pears.
It can be so but still can be known exactly how many letters can be entered after that number, if you enter letters and numbers before etc. ..
Re: Different rounding formula
I find also "CEILING".
Thank you.
Re: Different rounding formula
No,
I need a formula to to like this:
If a number (in cell A1) has no decimal in B1 to put that number (Eg A1=199 => B1=199)
If decimal are between 01 and 49 in B1 to put that number and decimal must be 5 (Eg A1=3.23 => B1=3.5)
If number in A1 is say 2311.5 in B1 it will be same number (2311.5)
If decimal are between 51 and 99 in B1 it will be number from A1 increased with 1 (Eg A1=338.62 => B1= 339)
Re: Different rounding formula
Hi,
Thank you for replay.
In cell A1 could be any number and I need a formula to return in B1 that "special" number :
If number in A1 is 125.17 (everything between 125.01 and 125.49) in B1 will be 125.5
If number in A1 is 125.50 in B1 will be 125.50 remain same number
If number in A1 is 125.69 (everything between 125.51 and 125.99) in B1 will be 126
If number in A1 is 125 Or 125.00 in B1 will be 125 remain same number
Hope now is clear.
Hi,
How can I do this:
In cell A1 is 5.00 in B1 will be 5.00
5.00 remain 5.00
5.19 becomes 5.5 (everything after the point and range between 5.01 and 5.49)
5.50 remain 5.50
5.83 becomes 4.00 (everything after the point and ranges between 5.51 and 5.99)
I made a long formula with IF, MOD and INT. Can somebody made formula for that?
Re: Count Specific Day Name Between 2 Dates
Quote from Baodad;644199I arrived here after Googling for how to find how many Mondays there are in a month. My solution is below. No UDFs, no array formulas. Mine only counts the number of Mondays in a month. I haven't tested it thoroughly, but it works for the 7 months I did test it on. I'm posting it here because others may be able to adapt it to their needs.
=ROUNDUP(((FirstDayOfNEXTMonth-FirstDayOfMonth)-(MOD(7-WEEKDAY(FirstDayOfMonth,3),7)))/7,0)
Hi,
How many Mondey is in a specified month.
Something like is this formula:
= 4+(DAY(A1-DAY(A1)+35)< WEEKDAY(A1-DAY(A1)-1))
(Monday=1,...Sunday=7)
Re: Time format hh:mm:ss.000
Quote from venkateswaran;642235Thanks all for ur effort. got the solution by using below formula.
= LEFT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-4)
Regards,
Venkateswaran K
Hello,
It's good that you answered this year
Solution found is text and you can not do any further calculations.
Happy New Year.
Re: Time format hh:mm:ss.000
Hi,
Try =RIGHT(LEFT(A1,FIND(".",A1)-1),8)*1
formatting cells according.
Re: ZipCode UserForm
Now is PERFECT.
Thank you so much.
Re: ZipCode UserForm
Thank you very much.
I can't make it work.
Even in this file OZ_Zip Code Userform.xlsm, comboBox2 is always empty.
Re: ZipCode UserForm
Hi,
Thanks for reply.
In attach is file ZipCode with dummy data.
Hello,
In Sheet1 A1: AP1 have names of regions. Under the name of each region (ie in A2: An) are localities in that region. Meeting is a userform that will display combobox1 regions, a combobox2 they will be displayed depending on the selected region localities in combobox1 and a textbox that will be displayed postal codes of the localities.
Postal codes are in Sheet2. In odd-numbered columns starting with A2, C2 etc are names of localities and in even-numbered columns are postal codes of the locality.
May be empty cells in column postal codes
Can anyone guide me or help me with VBA code.
Thanks in advance to all who help me.
Re: Workbooks in a folder and list the worksheets
Hi, Smallman
I made it. Thanks for VBA code.
Re: Workbooks in a folder and list the worksheets
hi Smallman,
How can I make the file name to be written in blue and bold font?