IF Formula Only Work If Particular Cell Is Less Than 50

Aug 7, 2014

See the attached workbook.

Please edit the formula in column B so that it only gives a BINGO if the adjacent cell in column G is less than 51.

So cell M16 should not be a BINGO because cell G16 is not less than 51.

Attached Files : Book1.xlsx‎

View 3 Replies


ADVERTISEMENT

Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

Jan 11, 2009

i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.

View 4 Replies View Related

IF Formula - Add A Number To A Cell For Work Breaks?

May 28, 2014

I am trying to add a number to a cell for work breaks. Below is what I am trying to do:

If F3 is < 4 then 0
If F3 is > 4 but < 6 then .25
If F3 is < 6 then .50

View 3 Replies View Related

VLOOKUP Wont Work If The Cell Contains A Formula

Mar 27, 2009

I have a VLOOKUP formula, so when the cell above (C5) has a value, it returns the value from a defined list. Simple so far and it works great when I manually input the value in the cell (C5).

The problem occurs when I change C5 to contain an AVERAGE formula? Any ideas why it will only work when there is a plain old value in there, but not a formula?

This is the VLOOKUP formula I'm using:

View 11 Replies View Related

Formula To Work Where It Will Search A Single Cell

Feb 27, 2009

I am trying to get a formula to work where it will search a single cell (on a sheet called "Calls") to see if a particular code is in there, if so return a value using HLOOKUP from a table on a sheet called "Values". The problem I am having is how to use the wild card etc because everything I have tried returns an error...

The code I am trying (Without wild cards, because they all failed) is...

Code: ....

View 10 Replies View Related

Copy A Formula Across Several Work Sheet And Have The Formula Always Take Data From Previous Work Sheet

Jan 2, 2009

I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.

2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations

View 2 Replies View Related

Using Formula With Cell Reference To Work In VLOOKUP Function

Jan 30, 2014

I have problem with a spreadsheet that I am trying to create.

I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.

Now for planning purposes, I would like to see how much of each item I have on order.

I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:

=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.

For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is

A108: ="A"&MATCH(D11,'purchase order'!A:A,0).

Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.

View 6 Replies View Related

Sum Formula In Cell "AD3" To Work

Nov 4, 2008

I'm attaching a file, that I can't seem to get the sum formula in cell "AD3" to work.

View 3 Replies View Related

IF Formula Does Not Work On The Last Part Of This Formula

Apr 17, 2007

This IF formula does not work on the last part of this formula
IF(E10>50,"$50.00",0)))) and I think there is a conflict with the
IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.

=IF(E8>0,"$0.00",IF(E9199,(E9*0.01),IF(E10>50,"$50.00",0))))

View 9 Replies View Related

Formula Does Not Work On Some Rows

Aug 11, 2014

I have some problem with my excel formula here. It works in some rows but doesn't in others.

I have attached the excel file herewith : Book1.xlsx

View 4 Replies View Related

Work Schedule Formula Using IF?

Jan 8, 2014

Combing these two formulas. I have a work schedule spreadsheet. If the employee is Off I want the value to = 0 (zero hours). But if the cell has a start time I need it to calculate those hours. I know both of the formulas work individually. But I need them to work together.

Formula for when cell says OFF:
=IF(C11="Off",0)

And the second

Formula to calculate hours (based on start time/finish time is the same for all employees)
=($X$4-C11+($X$4<C11))*24

View 2 Replies View Related

How Does This Formula Work- Sumproduct

Oct 18, 2008

=SUMPRODUCT(--($C$1:$C$17="S"),--($C$2:$C$18"S"))

how does this formula work?

It counts the number of instances in a list but not sure how

does the ranges have to be differant for this to work? ie C1:C17 C2:C18

View 9 Replies View Related

Formula Which Will Work Out 48 Permutations

Jan 17, 2007

I need to work out a formula which will work out 48 permutations.

There are three headings: Impact, Probability, Risk Category:

ImpactProbabilityRisk Category
VHM = 1
VHH = 1
VHVH = 1
HH = 1
HVH = 1
VHL = 2
HM = 2
MH = 2
MVH = 2
HL = 3
LVH = 3
MM = 4
LH = 4
ML = 5
LL = 5
LM = 5

The formula I need is: if Impact is "L" and probability is "M", then return answer = "5".

The thing is - there are 48 possible answers - and im not sure what formula you could type into 1 cell - which would answer this.

View 9 Replies View Related

Will VLookup Work With CountIfs Formula

Jun 23, 2014

I'm trying to count the rows of a worksheet based on the location selection. Heres what I'm using:

[Code]....

Before I add the Vlookup, I get a number of 50. I should get at least 40+, but after adding the

[Code] .....

I get "0."

I'm wondering if I need to use VLookup differently in this case ????

View 14 Replies View Related

Some Times If I Do A =if( ) Formula It Doesn't Work

Mar 12, 2008

What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?

Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.

View 11 Replies View Related

Why Wont Max Work On Cells That Have A Formula

Nov 19, 2007

On the attached form, for row 3 information is manually inputed into C3 and E3 to J3; K3 then looks at the max value inputed in E3 to J3 and alters L3 accordingly.

Row 4 is my attempt to automate the process. I am trying to alter the sheet so that the information added into C4 updates E4 to J4 automatically and then K4 looks at the max value and alters L4 accordingly.

Since I added the formula to H4 to look for info in C4, the max formula in K4 no longer works, as such L4 will not update.

View 14 Replies View Related

Formula That Will Work Out Score Of Individuals

May 31, 2012

I currently have a sheet of data with names and scores (1-10) on e.g.

NameScoreJohn10Smith5Steve4John9

and what i am wanting is a formula that will read these columns and tell me the amount of 1's, 2's, 3's, 4's, 5's, 6's, 7's, 8's, 9's and 10's the corosponding name has gotten.

for example:

JohnSmithSteve123415167891101

I have been playing around with COUNTIF formulas but cannot seem to work this out.

View 5 Replies View Related

Calculating Work Time Formula?

Jul 11, 2013

I'd like a calculation that gives me a total of 7.50 hours instead of 8:00 hours (I'm hoping to add up the time worked into a meaningful hour:minute format)

View 2 Replies View Related

SUM / IF And LEFT Formula Doesn't Work

Jul 27, 2013

im trying to use below formula using SUM and two IF conditions and 1 left formula

Cond 1
Cond 2
Value

22
m9
10

23
m9
10

[code]....

the following formula works to capture the sum of combination 22 and m9:

{=SUM(IF((A2:A6)=A8,IF(B2:B6=B8,C2:C6,)))}

22
m9
20

24
m8
10

however, in my original table, the 22, 23 etc are values with 6 numbers as shown below

Cond 1
Cond 2
Value

2201
m9
10

2301
m9
10

[code]....

following formula didnt work;

{=SUM(IF(LEFT(E2:E6,2)=E8,IF(F2:F6=F8,G2:G6,)))}

22
m9
0

23
m9
0

why the secund formula didnt capture anything when i added left to the first one.

View 6 Replies View Related

Can't Get Formula To Work When Put Inside A Loop

Apr 14, 2014

LastRow = Worksheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To LastRow
Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
If (Worksheets("Sheet1").Range("AE" & i).Value < 1.5) And _
((Worksheets("Sheet1").Range("K" & i).Value > 0) Or (Worksheets("Sheet1").Range("L" & i).Value > 0)) Then
Worksheets("Sheet1").Range("AE" & i).Font.Color = 255
End If
Next i

My loop here works just fine, and my Formula works in testing. However when I move my formula inside the loop I get an error.

Is the syntax wrong in this line = Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"

View 5 Replies View Related

Formula To Work Out Sum Depending On Number Of Weeks?

Jan 13, 2014

I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.

For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.

If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.

If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.

View 2 Replies View Related

Double Lookup Formula To Work With A Table

Feb 19, 2010

I am having trouble getting the double lookup formula to work with a table. See attached sample.

View 2 Replies View Related

Get Array Formula To Work In Shared Workbook?

Dec 12, 2012

is there a a way to get an Array formula to work in a shared workbook? when this was put in the workbook It worked perfectly but I didn't think about the fact the workbook was going to be shared !

The formula is:

Code:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(S$3:S$40,SMALL(IF(COUNTIF(L$3:L$38,S$3:S$40)=0,ROW(S$3:S$40)),ROWS(T$3:T3))-ROW(S$3)+1)))

View 4 Replies View Related

Formula- Sheet For Calculating Prices For My Work

Jan 17, 2009

I wanted to do a sheet for calculating prices for my work. I want to have 2 cells where in one I would type in manufacturer price and in other one I would get my price after markup. I have a list of price ranges that I don't know how to make a formula for. For example:

If price is $0-$74.99 than add $50
If price is $75-124.99 than add $75
If price is $125-174.99 than add $100
and so on til $7000

View 9 Replies View Related

TimeSheets :: Work Out Formula For Working Hours

Dec 27, 2009

Want to work out formula for working hours If Mr Smith is booked to work I must pay him a min of 8 hours pay at rate A then upto 10 hours still on rate A but after that he gets rate B I need to enter his data on a daily spreadsheet

View 9 Replies View Related

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

Mar 3, 2009

In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....

I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.

From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.

View 9 Replies View Related

Formula To Work Out Cost Including Multiple Discounts

Mar 31, 2014

complicated formula for working out prices including applying discounts.

I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19

I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.

View 2 Replies View Related

Formula Is To Work Out The Class Of An Honours Degree For A Certain Student

Mar 4, 2008

=IF(H29=>70,"1st",IF(H29>=60AND>=69,"2:1",IF(H29>=50AND>=59,"2:2",IF(H29>=40AND<=49,"3rd"))))

The formula is to work out the class of an honours degree for a certain student.

View 14 Replies View Related

SUMIFS Formula To Work Out If A Rep Is Below Target On Selling Brands

Aug 10, 2009

i am trying to us the SUMIFS formula to work out if a rep is below target on selling brands.

I have two worksheets summary and data, Summary worksheet is where i am trying to do the sumif formula against a sales average taking the information from the data worksheet
I have account manager name, brand, sales, DSI
I tryed the below sumifs formula but its coming back as 0

Please could someone have a look and point out the error in my ways
=COUNTIFS(data!$A$2:$A$11,B17,data!F2:F11,"<B2)")
The first count if should count how many times the account manager appears in the brand and the second count is if his DSI on the data worksheet is higher that the target dsi on the summary sheet

View 3 Replies View Related

Trim And Clean Function But Formula Doesn't Work?

Jan 31, 2014

I'm coping and pasting data (html) into a worksheet. When I try to format some of the columns as accounting$, number, ect. They won't format. It looks as if there are spaces before and after the data. So I tried the trim and clean function but it doesn't work, so I guess the blank "spaces" are not really spaces.

It gets better...

When I do the same exact copy and paste (as html) on another persons computer, they are able to format just fine, and the "spaces" are removed automatically.

Why is this working on one computer but not the other? Is it a setting that my computer has enabled or disabled? If so, what is it?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved