Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Conditional If Statement With Percent

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi everyone-

I'm trying to figure out the formula that will place the word "yes" if cell A is less than 25% of cell C.

For example, "Yes" if cell A1 (Quantity in stock) is less than 25% of C1 (Quantity in reorder), or "No" if this condition is no met.



View Answers     

Similar Excel Tutorials

Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Automatically Shade Every Other Row When You Add Data in Excel
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...
AND Function - Check if All Arguments are True
The AND function in Excel checks if every argument you put in it evaluates to TRUE.  If everything evaluates to TRU ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

Similar Topics

Dear all,

I would like to achieve the following:

M= minimum Stock
Res= Quantity in Stock after sales
In= Quantity to reorder

IF Res= to between 0- 25% of M than In= M- 0
IF Res= to between 25- 50% of M than In= M-15% of M
IF Res= to between 50- 75% of M than In= M-30% of M
IF Res= to between 75%-100% of M than In= M-60% of M


M= 30

=IF(D4<???,"30",IF(D4????,"45", IF(D4<????,"41",IF(D4<????,"42"))))

Thanks in advance for you help. Don't hesitate asking further details.

Best regards.

I'm still very new here & struggling abit with Excel, I know it can do lots of things, maybe because its late, my brain isn't helping me!!

I have created a Inventory Spreadsheet (I have attached it, to assist):

1) I want each piece of equipment to have its own unique number, eg. first 3 characters from "Manufacturer" column, first 5 characters from "Model Description" column & first 5 characters from "Part Number" column, and a number 1 going up from 1 from there.
& if there is nothing in these fields, the corresponding cell in "Column A" remains blank;

2) I would also like to create a traffic Light (red, orange, green) reorder system to monitor stock control. I have attempted to use "Conditional Formatting", but while it worked for the first line, it didn't work for the rest of the cells.

eg. If the "Quantity In Stock" column is higher than the "Desired Level" column then the corresponding "J" cell is Green
If the "Quantity In Stock" column is less than the "Desired Level" column then the corresponding "J" cell is Red
If the "Reorder Level" column is the same as the "Desired Level" column then the corresponding "J" cell is Orange


3) What is the formula to use if the "Unit Price" & "Inventory Price" columns have nothing in the cells, & I want them to remain blank until data is typed in.

I would appreciate any help anyone can offer.


I have this huge formula that I am using to create a field with text in it defining ingredients in food items. Is there anyway I can break it up?

Here's the formula:

=C2&IF(SUM(D2:E2)>0," -","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")

I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.

What I want to do is have three columns that interact with eachother.

1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status

What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".

So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".

Any help would be appreciated.

Hello All,

I have a inventory question that I need help with.

Lets say cell:
A1=Quantity in stock (person enters)
B1=Quantity used per day (person enters)
C1=Quantity used per week (person enters)
D1=Reorder Level (Person enters)
E1=Status (Condition formula)

What I need formulas for are
1. Subtract quantity used per day(b1) or quantity used per week (C1) from quantity in stock (a1). Then I want the total of this (a1-b1 or c1) to replace the quantity in A1 as the new quantity in stock.

After the calculation is completed I am looking for a formula that then clears the cells (B1 or C1) for the next entry (the next day/week).

2. A status formula that will state when A1 (quantity stock) is above the reorder level (d1) the cell (e1) will be green and say In Stock, otherwise if a1 is less than or equal to d1 then state Need to order, and turn cell e1 yellow.

If anyone can help I would really appreciate it!!! I would even pay for your time invested if I need to!!


Hi everyone, I have a rather large excel work book I set up for inventory control/ordering purposes, I have over 10,000 formulas in this work book. I have spent hours trying to get this particular IF statement to work with no luck. Any ideas?

Cell A-1 = 10 (Quantity in stock)
Cell A-2 = 15 (Quantity needed)
Cell A-3 = Order amount

I want cell A-3 to calculate the amount for me to order. Some times I have more in stock then the quantity needed. If there is more in stock then needed I want cell A-3 to = "0" not a negative number, if there is less in stock I need it to calculate the amount needed.

Here is what I have so far: =IF(A1>A2,0,A2-A1)

Thanks for the help

Hi everyone,
I have a nested IF function I'm having issues with. My workbook is laid out as follows:

Sheet1 contains two drop-down lists, in Cells E2 and F2, and a number field, G2.

The list in E2 may have the following values: "Condition 1" or "Condition 2". The list in F2 may have the following values: "Condition 3" or "Condition 4". Both are text fields.

I need to evaluate for the appearance of the following combinations: ("Condition 1" AND "Condition 3") OR ("Condition 1 AND "Condition 4"), ("Condition 2" AND "Condition 3") OR ("Condition 2 AND "Condition 4").

The results of the conditions use the value of the free-form number field, G2, and manipulate it referencing various values on Sheet3. I'm confident the results portion (the manipulations of G2) are correct, it's the formatting of the IF statement I'm having problems with.

I created the following statement to try to accomplish this:

=IF(AND(E2="Condition 1", F2="Condition 3"), G2*Sheet3!C1, IF(AND(E2="Condition 1", F2="Condition 4"),(G2*Sheet3!C1)*Sheet3!F4, IF(AND(E2="Condition 2", F2="Condition 3")(G2*Sheet3!C1)*Sheet3!F2 IF(AND(E2="Condition 2", F2="Condition 4") (((G2*Sheet3!C1)*Sheet3!F2)*Sheet3!F4), 0))))

However, it doesn't work, Excel tells me "The formula you typed contains an error." I've been searching this site but can't seem to find a readily available answer.

I realize I could be way off, or it could be an easy fix -- I'm new to more complex logic in Excel, so I'll thank you in advance for your patience with the newb.

Can anyone give me a correctly-formatted version of that statement, or point me to where I need to look to figure it out myself?

Many thanks,


in first cell (A1) i have a code: 553282300000
in second cell (B1) quantity: 50
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 160.

Here is the example:


553282300000............... 50........................55
553492300040............... 20
585281100060SMA........ 80

What i have to do is to sum the quantity of the products that begin with 55 in "C1", quantity of the products that begin with 58 in "C2" and the quantity of products that begin with 7E in "C3".

NOTE: OP requires VBA solution - WF solution provided previously he related thread:

This is killing me. I've read and read and read. I can't figure this one out. I konw it is super simple.

I have 2 columns. "RE ORDER LEVEL" and "IN STOCK" I want the re order level to turn red when the in stock goes below the re order level. I can make the formula for 1 cel but I can't seem to specify a range. Every time I try to specify the range it says I can't and I should use something like =SUM I don't want a sum I want it to compare the in stock quantity with the reorder level and if instock is less than reorder turn re order red but I need to apply the same formula for 300 rows quickly. Can someone tell me where I'm going wrong here??? I'm sure this is a simple formula.


The numbers in B3=0, C3=2 and D3=0 the formula I'm using is:

=IF(B3="0",1,IF(B3="5",1,IF(B3="1",2,IF(B3="6",2,IF(B3="2",3,IF(B3="7",3,IF(B3="3",4,IF(B3="8",4,IF( B3="4",5,IF(B3="9",5))))))))))

For cell "B3" the formula works

Now for both the next two cells C3 & D3 the formula is:

=IF(C3="0",1,IF(C3="5",1,IF(C3="1",2,IF(C3="6",2,IF(C3="2",3,IF(C3="7",3,IF(C3="3",4,IF(C3="8",4,IF( C3="4",5,IF(C3="9",5))))))))))

=IF(D3="0",1,IF(D3="5",1,IF(D3="1",2,IF(D3="6",2,IF(D3="2",3,IF(D3="7",3,IF(D3="3",4,IF(D3="8",4,IF( D3="4",5,IF(D3="9",5))))))))))

Doesn't work and displays "FALSE" ?????

I don't the luxury of wasting time, the formula should work but I don't understand why it isn't, could anyone please help me. (Probably just stressed and tried.)

Thanks ahead of time,

Hi Excel gurus, I have a formula that says too long, heres my formula...
=LOOKUP(B141,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32 ,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65, 66,67,68,70,71,72,73,74,75,76,77,78,79,80,81,494,495,496,497,498,499},{"$11.00","$11.00","$9.50","$8 .00","$8.50","$15.00","$12.00","$21.50","$14.50","$21.50","$22.50","$18.00","$21.50","$26.50","$14.0 0","$14.50","$14.50","$19.50","$14.50","$14.50","$19.50","$9.50","$14.50","$28.50","$42.00","$40.00" ,"$14.50","$24.00","$18.00","$24.00","$9.00","$7.50","$42.00","$30.00","$33.50","$45.00","$25.00","$ 23.50","$21.00","$14.50","$7.00","$21.50","$23.50","$33.50","$9.00","$8.50","$21.50","$36.00","$36.0 0","$36.00","$8.00","$33.50","$30.00","$50.00","$58.00","$72.00","$12.00","$6.00","$21.00","$7.00"," $72.00","$30.00","$7.00","$15.00","$10.00","$35.00","$36.00","$40.00","$7.00","$3.00","$8.00","$25.0 0","$3.00","$4.50","$1.50","$6.00","$12.00","18","20","16","$25.00","$28.00","$25.00","$35.00","$25. 00","$18.00"})

Enter PLU here qty entered
81 16
79 18
39 21

the problem is I will have a lot more PLU numbers as time goes. No wI am stuck, please help thanx in advance...

I'm getting multiple errors trying to get this formula into a macro:
The first being an expected end of statement on the fourth comma of the
third line (at the end of the first If(OR statement)

Basically, I'm just trying to get the formula typed in to the cell as
part of a macro and may be missing an easier way to type it in. The
formula itself works fine if I manually type it into a cell. The macro
recorder says it cannot record it. Please help.

ActiveCell.Formula = "=AI2&IF(OR(AI2="",AJ2=""),"",", ")&AJ2&
IF(OR(AI2&AJ2="",AK2=""),"",", ")&AK2 & IF(OR(AI2 & AJ2 &
AK2="",AL2=""),"",", ")
& AL2 & IF(OR(AI2 & AJ2 & AK2 & AL2="",AM2=""),"",", ")
&AM2&IF(OR(AI2&AJ2&AK2&AL2&AM2="",AN2=""),"",", ") & AN2
& IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2="",AO2=""),"",", ")
& AO2 & IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2 &
AO2="",AP2=""),"",", ")
& AP2"



I am trying to put both item and quantity in the same column.
For example:

A1 has "5a"
A2 has "3b"
A3 has "4c"
A4 has "3a"
A5 has "2a"

I would like to make a formula in another cell to contain the sum of all "A"s.
So for the example above I would like the formula to return a value of 8.

Is that possible?

I'm looking to create a mailto link. The issue i'm having is with the 255char limit i believe. I'd like to enter quite a few email addresses so that they'll automatically be entered into outlook when I click on the link. Is there a way to do this? all the addresses come from a different row of a column. I've concatenated them all with a comma delimiter so that formula can be used to enter after the mailto.

What I have:

=hyperlink("mailto:" & =$K$12&IF($K$13<>"",","&$K$13,"")&IF($K$14<>"",","&$K$14,"")&IF($K$15<>"",","&$K$15,"")&IF($K$16<>"" ,","&$K$16,"")&IF($K$17<>"",","&$K$17,"")&IF($K$18<>"",","&$K$18,"")&IF($K$19<>"",","&$K$19,"")&IF($ K$20<>"",","&$K$20,"")&IF($K$21<>"",","&$K$21,"")&IF($K$22<>"",","&$K$22,"")&IF($K$23<>"",","&$K$23, "")&IF($K$35<>"",","&$K$35,"")&IF($K$36<>"",","&$K$36,"")&IF($K$37<>"",","&$K$37,"")&IF($K$38<>"",", "&$K$38,"")&IF($K$39<>"",","&$K$39,"")&IF($K$40<>"",","&$K$40,"")&IF($K$41<>"",","&$K$41,"")&IF($K$4 2<>"",","&$K$42,"")&IF($K$43<>"",","&$K$43,"")&IF($K$44<>"",","&$K$44,"")&IF($K$45<>"",","&$K$45,"") &IF($K$46<>"",","&$K$46,"")&IF($K$47<>"",","&$K$47,"")&IF($K$48<>"",","&$K$48,"")&IF($K$49<>"",","&$ K$49,"")&IF($K$50<>"",","&$K$50,"")&IF($K$51<>"",","&$K$51,"")&IF($K$52<>"",","&$K$52,"")&IF($K$53<> "",","&$K$53,"")&IF($K$54<>"",","&$K$54,"")&IF($K$55<>"",","&$K$55,"")&IF($K$56<>"",","&$K$56,"")&IF ($K$57<>"",","&$K$57,"")&IF($K$58<>"",","&$K$58,"")&IF($K$59<>"",","&$K$59,"")&IF($K$60<>"",","&$K$6 0,""))

I've also tried to do things like display all the emails so that they can be copied to the clipboard after the excel file has been saved and then viewed as a webpage. This runs into the 1024char limit when leaving the concatenated addresses in a cell or even a text box. any fancy idea on a way to accomplish any of this?


Hi, i hope someone has the answer to my question.
what im trying to do is:
make a formula that will display a word in its cell & another word in a
different cell.
meaning: if i am typing in cell C1 and i use the if statement to compare two
values: IF(A1>B1) if this is true then display the word "OFF" in its own
cell: this part is easy
i would also need it to display the word "ON" in cell D1
if the statement is false, i dont need anything displayed in any cells.
my formula is: IF((A1>B1),("OFF"),("") --> this will display the word "OFF"
in cell C1 if the statement is true and nothing in its cell if the statement
is false. but i dont know how to display the word "ON" in cell D1 at the same
its displaying the word "OFF" in cell C1.

Thank you in advance.

Hey guys, if working on a small project right now, and i'm a bit lost.
here is what i need to do.

Column A= Item
Column B= Quantity In-Stock
Column C= Quantity Subtracted

i need a macro that would take "column C" subtract it from "column B", and then clear "column C". if the macro could do all the rows simultaneously, as apposed to having a macro for each row that would be great.

thanx for all the help in advance.


I have a stupid long forumla that needs to be longer! There must be an easier way of doing and I'm hoping one of you guys can sort it.

=IF((AND(AF4="w",AG4="w")),"0")*(IF((AND(AF4="w",AG4="1c")),"1"),(IF((AND(AF4="w",AG4="1b")),"2",(IF ((AND(AF4="w",AG4="1a")),"3",(IF((AND(AF4="w",AG4="2c")),"w",(IF((AND(AF4="w",AG4="2c")),"4",(IF((AN D(AF4="1c",AG4="w")),"-1",(IF((AND(AF4="1c",AG4="1c")),"0",(IF((AND(AF4="1c",AG4="1a")),"1"),(IF((AND(AF4="1c",AG4="1a"))," 2"),(IF((AND(AF4="1c",AG4="2c")),"3"),(IF((AND(AF4="1c",AG4="2b")),"4"),(IF((AND(AF4="1c",AG4="2a")) ,"5"),(IF((AND(AF4="1b",AG4="w")),"-2"),(IF((AND(AF4="1b",AG4="1c")),"-1"),(IF((AND(AF4="1b",AG4="1b")),"0"),(IF((AND(AF4="1b",AG4="1a")),"1"),(IF((AND(AF4="1b",AG4="2c")) ,"2"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c" )),"5"),(IF((AND(AF4="1a",AG4="w")),"-3"),(IF((AND(AF4="1a",AG4="1c")),"-1"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c")) ,"5"),(IF((AND(AF4="1a",AG4="w")),"-3"),(IF((AND(AF4="1a",AG4="1c")),"-1"))))))))))))))))))))))))))))))))))

Any ideas?

I have a column of values that are based on a formula e.g.


What I am trying to do is place a conditional format on cell A1

1. if cell is =>50 make cell RED,
2. if cell is between 40 to 49 make cell Green
3. if cell is "" then make cell Yellow

The first two work okay but are having trouble with the third condition.
Have tried Cell equals "" and Formula Is =A1="" but without success.

Can anyone help me here please.

I would like this code to check if there is already something in the cell, and if so, add a comma and space, then output the next appropriate condition of the if statement. The way I have the code written currently, it checks one column to see if a word exists, then if so, it outputs a word associated with this condition. Under this method, the code does not take account for columns that have more than one matching word. I've included a small portion of the code, there are more elseif statements, but they are redundant for this purpose. Thanks in advance for your help!!!


Sub Industry()

'Change this to reflect your true starting row.
vStartRow = 8

For i = vStartRow To Cells(Rows.Count, "M").End(xlUp).Row

    If InStr(1, Range("M" & i).Value, "UL 60950") Or InStr(1, Range("M" & i).Value, "UL 60950-1") _
        Or InStr(1, Range("M" & i).Value, "UL 1459") Or InStr(1, Range("M" & i).Value, "UL 497") _
        Or InStr(1, Range("M" & i).Value, "UL 497A") Or InStr(1, Range("M" & i).Value, "UL 497B") _
        Or InStr(1, Range("M" & i).Value, "UL 497C") Or InStr(1, Range("M" & i).Value, "UL 1863") _
        Or InStr(1, Range("M" & i).Value, "UL 1310") Or InStr(1, Range("M" & i).Value, "UL 1012") _
        Or InStr(1, Range("M" & i).Value, "UL 61965") Or InStr(1, Range("M" & i).Value, "UL 60601-1") _
        Or InStr(1, Range("M" & i).Value, "UL 60601A-1") Or InStr(1, Range("M" & i).Value, "UL 60601B-1") _
        Or InStr(1, Range("M" & i).Value, "UL 60601C-1") Or InStr(1, Range("M" & i).Value, "UL 1863") Then
            Range("N" & i).Value = "ITE"

'Insulating Materials
    ElseIf InStr(1, Range("M" & i).Value, "UL 94") Or InStr(1, Range("M" & i).Value, "UL 840") _
        Or InStr(1, Range("M" & i).Value, "UL 1446") Or InStr(1, Range("M" & i).Value, "UL 2353") _
        Or InStr(1, Range("M" & i).Value, "UL 1692") Or InStr(1, Range("M" & i).Value, "UL 224") _
        Or InStr(1, Range("M" & i).Value, "UL 1441") Or InStr(1, Range("M" & i).Value, "UL 1696") _
        Or InStr(1, Range("M" & i).Value, "UL 969") Then
            Range("N" & i).Value = "Insulating Materials"

End If
Next i

End Sub

I've a Database Query with Command Text that looks like this (Command Type SQL);

ILE."Item No_",
ILE."Posting Date",
ILE."Entry Type",
ILE."Location Code",

"Production".dbo."Item Ledger Entry" ILE

ILE."Item No_" In (?)

I can then set to "Get the value from the following cell" in Parameters for the above question mark in the WHERE statement. That works well if I only type a single "Item No" in my cell (for example my parameter cell is cell I2, and in that cell is FE02C). However, I'd like to have a list of acceptable values in cell I2, such as FE02C, FE02D, FE0ZZ, etc.,.


Good day: I am using the following formula to make choices for conditional
formatting in other cells. As it is, the "else" would be the result of an
entry of a US state abbreviation and would result in a 2 being placed in the
cell. The problem is that if the source cell is blank a 2 is returned. Is
there another way of expressing this so that a Canadian province abb =1, a US
state = 2 and a blank cell ="" null.
Thanks Kevin

I have a pivot table that shows the quantity of an item on hand for various locatons. I want to count the number of occurences where the quantity is less than the reorder point. I use a vlookup function to get the reorder point for each item.

For example

Store Location---A B C D E F Reorder Count
part #123------- 5 2 1 6 1 3 4 4

That is, the values in locations B, C, E and F are below the reoder point.

I can construct a countif function =countif(B2:G2,"<4") however the reorder point for the next item might be 10.

Is there a way in which I can say countif the value is less than the value stored in cell H2?

That is =countif(B2:G2,<h2)

Thanks in advance for your assistance.

Jim Palmer

Respectable Greeting to all

I stuck in this type of IF Condition, can plz anyone help

"Subtract Cell ""G4"" from Cell ""I4"", IF Cell ""J4"" become equal to Cell ""G4"", then
Subtract Cell ""G5"" from Cell ""I4"", IF Cell ""J5"" become equal to Cell ""G5"", then
Subtract Cell ""G6"" from Cell ""I4""."

For e.g
SUM(IF((G4-(H4+I4)="G4" Then Shift to Cell (G5-(H5+I4)="G5") Then Shift to Cell (G6-(H6+I4)))

please find the attachment

Leave Balance From Half Day.xlsx

best Regards

Hi Guys,

I will really appreciate you help. I have been breaking my head for an hour or so to figure this out. But no luck!

Here is what i am trying to do


In G6 we mannualy input depending on the following category.

I have 3 categories Cars (C), Vans (V) and FFV i want to perform the above calculation and if the result is greater than $2000 then the function should return max $2000 as the output.

I tried using the max function as follows

But i keep getting -2000 as output even when i change values in cell E55.

Let me know if further clarification is required.

Thanks in advance.

- Aamil

Example Excel: Example.xlsx.
Below is the function I am using that is not working properly.{ =IF(A11=A10,"",IF(A11=A12=A13=A14=A15,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&";"&C14&"-"&D14&"-"&E14&"("&F14&"-"&G14&")"&" "&C15&"-"&D15&"-"&E15&"("&F15&"-"&G15&")",IF(A11=A12=A13=A14,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&" "&C14&"-"&D14&"-"&E14&"("&F14&"-"&G14&")",IF(A11=A12=A13,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&,IF(A11=A12,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"))))) }

The function works in most ways except for one, it does not combine more than a few pieces of information. I tried using the =CONCATENATE() function instead of using [&] but had the same result. If I pull out the function that combines data that works properly:
{ C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&";"&C14&"-"&D14&"-"&E14&"("&F14&"-"&G14&")"&" "&C15&"-"&D15&"-"&E15&"("&F15&"-"&G15&")}
Is there an alternative method to solve this problem? Is there someone to change a setting so this actually works?
I have a set of data with an identification code, I need to reduce the data down so there is only one identification code per the rest of the corresponding data. My function checks if there are multiple entries with the same identification code and combines the corresponding data into one cell.

I believe excel is incapable of handling the function.