Free Microsoft Excel 2013 Quick Reference

Count text as value of 0.5 not one

Hi All,

Being sratching my head on how to do this. I've been mixing up my formulas, but all I get is #Value!.

On the roster I enter the staff members name and the cell below counts the staff members name (that simple) but the boss wants new working hours which involves closing on the half hour.

The old code is
I need to change it to 

=COUNTIF(D$12:D$22,"Staff Member") then add cell D23 count as 0.5 (half an hour)

Thanks

ratcat


Post your answer or comment

comments powered by Disqus
Have a sheet with 0.00 in column D

My normal routines for hiding rows with a cell value of 0 do not provide the result needed.

Earl

Hello,

I am encountering an issue when trying to calculate a text as value in a formula.

In cell A5 (please see the excel attached) I have a weighted formula that is supoosed to return a final result based on the drop down from cells B5 to V5. Drop downs give the option of choosing a value. Everything is going fine as long as you select a value (5, 3 or 0).

In cells H5, J5, K5, R5, S5, U5 there is also the option of choosing NA. I am trying to get the formula to calculate NA as being equal to 5. Basically, if for the respective parameter someone choses NA, the final result in cell A5 should not be influenced.

Eg.: If all the parameters are 5 and only one has NA (any of the cells H5, J5, K5, R5, S5, U5), the final result (in cell A5) should be 100.
The same applies if all parameters are 5 & more than one cell or all the cells that have the NA options (H5, J5, K5, R5, S5, U5) are NA.

If you have any idea, please let me know.

Thank you for your help.

Regards,

liviu25

I have a sheet with 2884 rows.. I am currently using the following macro (with changes for each column) to search columns AN - BG and delete cells with a value of 0 and move the columns that do not = 0 up..

for example:

0
0
0
0
5
0
0
3
0
0
0
1

would be

5
3
1

I can not delete the entire row, because the next column may not have a 0 value in the same row..

Obviously, the process I have below takes FOREVER. Is there a faster way to do this? Again, it is Column AN through Column BG and rows 6-2884 . I am going through it backwards so that I do not miss any 0's.

Dim ANcell As
Range
Dim ANCount As Long
  For ANCount = 2884 To 6 Step -1
  'Start from bottom of range, so we don't skip any rows When we've deleted one
  If Trim(Range("AN" & ANCount).Value) = "0" Then
     Range("AN" & ANCount).Delete Shift:=xlUp
  Else
  End If
  Next ANCount


Tryign to setup a chart that would give me a running total of data only if =1
but give me a value of 0 if =0. I've been able to setup the running total of
data but when it comes to a 0 it just gives me the pervision number the
conitunes. Here what I mean.

Problem What I would like
Data Calc. Data Calc.
0 0 0 0
1 1 1 1
1 2 1 2
1 3 1 3
0 3 0 0
1 4 1 1
1 5 1 2

Hi,

I've problem. I have table where is simple SUM-function in macro. It works fine with my computer and also in another computer. But there was also third computer and there was a problem. when I try run macro in that computer it always counts SUM as value zero.

33 33
22 22

55 0

If I remake a SUM function with excel value still is 0.

Excel 2000, version is same in all three computers.

-janne-

this is my whole string of code, the bottom is the trouble i am having, it gives me a return value of 0 when i would want it to be 2000 or 3000. this is an input amount. the code in red is where i am having trouble
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Target.Address
        Case "$B$15"
            Range("B16").Formula = "=B15/B7"
        Case "$B$16"
            Range("B15").Formula = "=B7*B16"
        Case "$D$15"
            Range("D16").Formula = "=D15/D7"
        Case "$D$16"
            Range("D15").Formula = "=D7*D16"
        Case "$C$15"
            Range("C16").Formula = "=C15/C7"
        Case "$C$16"
            Range("C15").Formula = "=C7*C16"
        Case "$B$32"
            Range("B33").Formula = "=B32/B24"
        Case "$B$33"
            Range("B32").Formula = "=B24*B33"
        Case "$D$32"
            Range("D33").Formula = "=D32/D24"
        Case "$D$33"
            Range("D32").Formula = "=D24*D33"
        Case "$C$32"
            Range("C33").Formula = "=C32/C24"
        Case "$C$33"
            Range("C32").Formula = "=C24*C33"
        Case "$B$54"
            Range("B55").Formula = "=B54/B46"
        Case "$B$55"
            Range("B54").Formula = "=B46*B55"
        Case "$C$54"
            Range("C55").Formula = "=C54/C46"
        Case "$C$55"
            Range("C54").Formula = "=C46*C55"
        Case "$D$54"
            Range("D55").Formula = "=D54/D46"
        Case "$D$55"
            Range("D54").Formula = "=D46*D55"
        Case "$B$70"
            Range("B71").Formula = "=B70/B62"
        Case "$B$71"
            Range("B70").Formula = "=B62*B71"
        Case "$C$70"
            Range("C71").Formula = "=C70/C62"
        Case "$C$71"
            Range("C70").Formula = "=C62*C71"
        Case "$D$70"
            Range("D71").Formula = "=D70/D62"
        Case "$D$71"
            Range("D70").Formula = "=D62*D71"
        Case "$B$82"
            Range("B83").Formula = "=B82/B74"
        Case "$B$83"
            Range("B82").Formula = "=B74*B83"
        Case "$C$82"
            Range("C83").Formula = "=C82/C74"
        Case "$C$83"
            Range("C82").Formula = "=C74*C83"
        Case "$D$82"
            Range("D83").Formula = "=D82/D74"
        Case "$D$83"
            Range("D82").Formula = "=D74*D83"
        Case "$E$15"
            Range("E16").Formula = "=E15/E7"
        Case "$E$16"
            Range("E15").Formula = "=E7*E16"
        Case "$D$15"
            Range("F16").Formula = "=F15/F7"
        Case "$F$16"
            Range("F15").Formula = "=F7*F16"
        Case "$G$15"
            Range("G16").Formula = "=G15/G7"
        Case "$G$16"
            Range("G15").Formula = "=G7*G16"
        Case "$E$32"
            Range("E33").Formula = "=E32/E24"
        Case "$E$33"
            Range("E32").Formula = "=E24*E33"
        Case "$F$32"
            Range("F33").Formula = "=F32/F24"
        Case "$F$33"
            Range("F32").Formula = "=F24*F33"
        Case "$G$32"
            Range("G33").Formula = "=G32/G24"
        Case "$G$33"
            Range("G32").Formula = "=G24*G33"
        Case "$E$54"
            Range("E55").Formula = "=E54/E46"
        Case "$E$55"
            Range("E54").Formula = "=E46*E55"
        Case "$F$54"
            Range("F55").Formula = "=F54/F46"
        Case "$F$55"
            Range("F54").Formula = "=F46*F55"
        Case "$G$54"
            Range("G55").Formula = "=G54/G46"
        Case "$G$55"
            Range("G54").Formula = "=G46*G55"
        Case "$E$70"
            Range("E71").Formula = "=E70/E62"
        Case "$E$71"
            Range("E70").Formula = "=E62*E71"
        Case "$F$70"
            Range("F71").Formula = "=F70/F62"
        Case "$F$71"
            Range("F70").Formula = "=F62*F71"
        Case "$G$70"
            Range("G71").Formula = "=G70/G62"
        Case "$G$71"
            Range("G70").Formula = "=G62*G71"
        Case "$E$82"
            Range("E83").Formula = "=E82/E74"
        Case "$E$83"
            Range("E82").Formula = "=E74*E83"
        Case "$F$82"
            Range("F83").Formula = "=F82/F74"
        Case "$F$83"
            Range("F82").Formula = "=F74*F83"
        Case "$G$82"
            Range("G83").Formula = "=G82/G74"
        Case "$G$83"
            Range("G82").Formula = "=G74*G83"
    End Select
    Select Case Range("$B$3")
        Case 1
            Range("$B$4").Formula = "=B4*1"
        Case 2
            Range("$B$4").Formula = "=B4*B3"
        Case 3
            Range("$B$4").Formula = "=B4*B3"
        Case 4
            Range("$B$4").Formula = "=B4*B3"
        Case 5
            Range("$B$4").Formula = "=B4*B3"
        Case 6
            Range("$B$4").Formula = "=B4*B3"
        Case 7
            Range("$B$4").Formula = "=B4*B3"
    End Select
    Application.EnableEvents = True
End Sub


Hi,

I`m building a macro which scans certain cell wheter they have info in it or not. These cells are replica`s of other cells in cell B12 I have "=A12"
However if the base cell is empty (a12), the copied cell (b12) gives a value of 0. The macro consequently scans it and determines is is "notempty"

I cannot seem to set the 0 to empty. (cell formats etc)Can anybody help me?

Delete duplicate records with sum =0 or delete unique records with value of 0

I would like to find a way in either access or excel to delete duplicate records with amounts that add up to 0 or unique records with amounts of 0 and leave the remaining records that do not add up to 0. Any help in the form of a formula or visual basic code would be greatly appreciated. Thanks in advance.

List before process
Id # ---------------------- Amt $
0001567891____ |______ -6800
0001567891____ |______ 6800
0001567891____ |______ 6800
0001567891____ |______ -6800
0001567891____ |______ 6800
0001567891____ |______ 5500
0001567891____ |______ 5500
0001567891____ |______ 0
0005555555____ |______ 0
0005555555____ |______ 3600

List after process
Id # --------------------- Amt $
0001567891____ |______ 5500
0001567891____ |______ 5500
0005555555____ |______ 3600
0001567891____ |______ 6800

=IF(P148<0,MROUND(P148,-0.5),MROUND(P148,0.5))

Cell P148 is an average of 4 different numbers. I used the above equation to round the value to a multiple of 0.5 because I want all the final values to be a multiple of 0.5 (the number can be an integer or a decimal ending in .5 only).

However, the mround function always rounds up. I want it to round to the nearest integer when cell P148 ends in 0.75 or 0.25, so I want it to round down when P148 ends in 0.25 and round up if P148 ends in 0.75.

How do I do this?

Hi,
Basically i want a cell to raise by increments of 0.5 depending on the value of another cell
I 've tried figure out a calculation to do this but to no avail unless i'm missing something simple

If there are 4 hours in a half day, i need to divide the hours cell by that number to get the number of
half days then get a second cell to display that in increment of 0.5 so: -
4 hours would = 0.5
8 hours would = 1.0
16 hours would = 2

but heres the tricky bit

anything between the divisables of 0.5 should be roundedup to the nearest 0.5 so:
5 hours would = 1.0
6 hours would = 1.0
9 hours would = 1.5
17 hours would = 2.5
etc

any ideas?

Thanks

Hi,

I am trying to copy automatically the value from one cell in lets say
worksheet 1 to another cell in worksheet2. In order to do that I use the
‘Sheet1’!A1 function in the desired cell in Spreadsheet 2. This function
copies the value to the desired cell in Spreadsheet 2 though this value
doesn’t have the same format as the value in the cell of the Spreadsheet1.
For example a null value in Spreadsheet1 appears as 0 in Spreadsheet 2.

Is there any other function that I can use to perform this task? Do you know
any other AUTOMATIC way to copy the value of a cell in one spreadsheet to a
cell in another spreadsheet but keeping the same format?

Please help.

Thank in advance.

Regards
Michael

Hi,
I have one column in which number entred will be decimal number with one digit after decimal.
I want to apply a validation on this column so that data entred in this column should be multiple of 0.5.
How can I do this.?

Is it possible to increase the value of all cells in one row on the same number, for example on 10?

Is it possible to do in in the MS Word table also ?

Hello, I have created a cashout sheet in excel that writes data to a database on a separate sheet in the same workbook through a save button. I’m working on a load button that can call that information back into the sheet. I have run into a problem when re-populating the cells with information, if there is a value of zero I want the cell to be blank. The way it works right now is it repopulates everything the way I want it to look but the corresponding formulas that figure out percentages and other values show up as #value and don’t process it properly. If I go into the blank cells and hit backspace it seems to clear something and the formulas work again. The cell it looks at to get the value from has a formula in it that looks like this to make it blank =IF(A3=0,"",A3). The code that transfers the data looks like this

	VB:
	
[FONT=Calibri][SIZE=3] 'ws2.Cells(10, 14).PasteSpecial (xlPasteValues)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3][/SIZE][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I’ve also used

	VB:
	
[FONT=Calibri][SIZE=3]ws2.Range("n10") = ws3.Range("a4")[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3][/SIZE][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I’m wondering what is being transferred to cells that are not allowing it to process correctly on the sheet and how I would make it so it works. Thanks ahead of time for any help.

Someone in our office has an interesting condition that I cannot seem to solve. Whenever they type a simple formula in their spreadsheet, the value keeps returning a 0. When you type something like 10/2 the value returns as 5. Once the = sign is used (=10/2), the value is 0. This also happens when we try to reference other cells (i.e. =k12/1500 will also return a value of 0.)

I suspect it is a formatting issue, but I am not seeing it. From what I can tell, text cells are formatted as "General" and numbers are formatted as numbers.

Thanks

Hi guys,

My father in law has asked me to look at his pool clubs score sheet, two columns at the end register the number of wins and losses and the losses column is the problem.

Basically if the loose more than half their games in any given match so the column value is 0-4 as they enter the number of wins it counts a loss and adds 1 to the cell using this formula

=IF(Q5<1,0,IF(Q5<5,1,IF(Q5>4,0)))+IF(R5<1,0,IF(R5<5,1,IF(R5>4,0)))+ ect for up to the AA column

The problem is if they loose all their games it wont register a 0 as a loss but when he adjusts the formula to count zeros it registers all blank cells as a loss which makes them look like they have lost all their games at the start of the season.

I was thinking of allowing it count 0's and blanks then hiding this column and adding another that which counts the blank cells and subtracts them from the value in the original column.

I have a feeling this is over thinking it and any pointers at all would be very gratefully received

Thanks

I am trying to use a literal cell reference as a part of the criteria in the
SUMIF function, and i can't get it to sum properly. It only seems to like an
actual value, not a cell reference, to work properly. Does anyone know a
roundabout for this?

Sample table:

A B C
1 2
2 100 5
3 15 5
4 10 4
5 5 4
6 15 3
7 10 3
8 5 2
9 25 2
10 10 1
11 3 0

SUMIF(B2:B11,">=2",A2:A11) works fine
SUMIF(B2:B11,">=$C$1",A2:All) doesn't work, returns a value of 0 in another
spreadsheet (not the same cell values as the example, but the same
structure). I want to use C1 so i can change the criteria in the SUMIF
without having to change each and every reiteration of the SUMIF i have in
different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5.

any recommendations? have you run into this issue before?

I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what I'm
looking up), all calculations attached to that cell give me #N/A's as well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,">0") in another cell that points to my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place the
vlookup function inside of the SUMIF.

Thanks for your help!

Hi,
I am new to writing VBA macros. I am trying to copy a column selected by user in my macro and export it to file. I use the following lines for the selection
-------------------------------------
1. ' Loop for each row in selection
2. For RowCount = 1 To Selection.Rows.Count
3.
4. ' Loop for each column in selection
5. For ColumnCount = 1 To Selection.Columns.Count
6.
7. TempString = Selection.Cells(RowCount, CoumnCount).Text
8. Print #FileNum, TempString
9.
10. ' Start next iteration of ColumnCount loop
11. Next ColumnCount
12.
13. ' Start next iteration of RowCount loop
14. Next RowCount
---------------------------------------------------
In Line 7 of the code above I want to copy the "value" of the cell not what it is linked to via a formula which in turn has a formula. The cell I am trying to copy the value of has a formula to another sheet in the workbook like this
"="Sheet1"!E10".

Thanks,
excel_m

In each cell of Column M is forumla for the time to fill for positions. I want the AVERAGE of Column M but I do not want to include cells that have a value of "0" or "n/a". I've tried COUNTIF, AVERAGEIF, and SUM but can't find the right combination. Example Below:

1) 90
2) 30
3) n/a
4) 110
5) 0
6) n/a
7) 60
Sum = 290
By excluding "0" and "n/a" my average should be 72.5

Please help - Thanks!

I am using Excel 2003 SP1.

I am entering the number of hours it takes to do a task into a cell as 1.5

I want it to display as 01:30 ( 1hr:30min )

I can do this by entering 0.5 in one cell and then converting it to a text
01:30 but I would rather loose the additional coloum to do this by just
formating the cell.

Can this be done some how

I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what I'm
looking up), all calculations attached to that cell give me #N/A's as well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,">0") in another cell that points to my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place the
vlookup function inside of the SUMIF.

Thanks for your help!

Hello everyone,

I have a basic question regarding VBA in Excel 2007.
The situation is following:

My excel contain user defined function
Public Function EStorm(sData As String, sWaluta As String) As String
    If Len(sWaluta) = 0 Then sWaluta = "EUR"
    If Len(sData) = 0 Then sData = "2011-04-01"
    
    Dim sResult As String
    
    Dim myURL As String
    myURL = "http://e-storm.pl/kursy/" & sWaluta & "/" & sData
    sResult = GetDataFromURL(myURL, "GET", "")

    If sResult = "0" Then
        'sData = Format(Format(DateAdd("day", -1, DateValue(sData)), "Short Date"), "String")
        'sResult = EStorm(sData, sWaluta)

        'MsgBox "ActiveCell.Value = " & Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
        'MsgBox "ActiveCell.Value = (" & ActiveCell.Row - 1 & ", " & ActiveCell.Column &
") = " & Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
        'sResult = Worksheets("Kurs").Cells(Cell.Row - 1, Cell.Column).Value
        'sResult = Cells(Target.Row - 1, Target.Column).Value
    End If
    
    EStorm = Replace(sResult, ".", ",")
End Function
GetDataFromURL is working function found here.

Now I have a worksheet containing a list of dates (in format 2011-04-01 - 2011-04-30) in column A and functions in column B (for B1 it would be =EStorm(TEXT(A1;"rrrr-mm-dd");"EUR")).
It works quite well, except the fact that for weekends it provides value of 0.

As you can see in the comments of EStorm, I have tried to wrap the result by rewriting value of the cell above to the cell that invokes the function if value of 0 is returned. The problem is I don't know how to address the cell that contains my function.

It is not active cell because most of the time I work in different worksheet. The values are updated in the background, on workbook open or change.

Please help if possible. The question is highlighted

I have a great spreadsheet that's really been a life-saver. Just one thing
I'd like to fine-tune. In column G, specifically in cell G3, I have the
following code:

=IF(E3<>"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results are funny in one
way: to show "negative" hours in results, I changed to the 1904 system for
this workbook as per advice from this ng. So that works beautifully. When
less time is worked during day, the total regular hours worked show as a
negative which is then taken into account in the "overtime". The actual
overtime worked, then, shows "real" overtime worked and not just a result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column displays as 0h00m and
sometimes it shows up as -0h00m. I haven't figured out why it sometimes
puts that negative sing in. Ultimately, the mathematical end result of "0"
is the same, of course, but that makes this column awkward. It was this in
part that prompted me to wish this formula displayed results differently.

Since this is a visual check calculation only in this cell so that I can see
if a workday has been worked in its entirety, it actually would be better if
the above code only displayed for any other value other than "0". Is there
a way, then, to incorporate that into the above formula? i.e., that the
cell will be also be completely blank unless there is a resulting value
other than 0h00m or -0h00m?

Thanks much! :oD


No luck finding an answer? You could always try Google.