Need Help with IF Function,And Function & Mod Function

The Excel sheet has been edited, please go back to the original post.

thanks in advance,,,

in Column C, we can use " Data validation" from ( data tab ), the objective is just to control the input amount, not to enter any amount.

please have a look at the attachments.

Thanks in Advance,,,

Attachment 852964

Attachment 852965

the suggested equation doesn't meet the condition of ( multiplier ) , if you enter any amount, it ignores it.

please check the (multiplier condition ) in the Mod function and compare it with the original one.

Thanks in advance,,,

If you enter any amount, whatever is it, for example( 330 or 250.50, ......etc), unfortunately, no error messages are shown, however, the minimum amount to enter is 1000, and NOT less than 1000 is acceptable.

the equation is true except ( multiplier condition ), just to activate the option of validating the input amount ( the minimum amount is 1000).

Thanks in Advance,,,

Thank you so much for helping, I actually tried the new equation, and unfortunately, there is only one problem which is as follows:-

if you try to input an amount ( 250000) and its multiplier (10000), it doesn't work, however, the input amount is correct
all other conditions are working.

The new equation that I'm using after I updated it according to ( multiplier ):-


IF(AND(C1<100000,MOD(C1,1000)=0),C1,IF(AND(C1<250000,MOD(C1,10000)=0),C1,IF(AND(C1<1000000,MOD(C1,100000)=0),C1,IF(AND(C1<3000000,MOD(C1,100000)=0),C1,IF(MOD(C1,100000)=0,C1,"Invalid Amount")))))


Thanks in Advance,,,

Mohamed, it is really a bad practice to use empty columns and row to divide a table, in the attached file I offer a correction.

Also it seams to me that you are doing a circular reference.

Try this formula,
G7=IF(AND($C$1>=--TRIM(LEFT(SUBSTITUTE(TRIM(C7)," ",REPT(" ",100)),100)),MOD($C$1,--TRIM(MID(SUBSTITUTE(TRIM(C7)," ",REPT(" ",100)),FLOOR(LEN(SUBSTITUTE(TRIM(C7)," ",REPT(" ",100))),100),FLOOR(LEN(SUBSTITUTE(TRIM(C7)," ",REPT(" ",100))),100)+100)))=0),$C$1*E7,"Nil"), copy down.

Agreed with DJunqueira in post #12, it is really a bad practice to use empty columns and rows to divide a table.

Replying to my own post, but this would be my solution attached.

Sorry for being late, thanks for more clarification, I will send you my final Excel sheet that shows what I mean, please have a look, especially Cell B1.

Cell B1, I used as the primary cell for any inputted amount, the equation used you will find the following path:-
click on " Data Tab", then from data tools click on " Data Validation ", then "Settings" then "Formula".


the challenges that I face right now are as follows "-

trying to input one of the following amounts: ( 250000, 270000, 290000, 310000, 330000, 350000, 370000, 390000, 410000 ), the error message is shown, however, these amounts met the other condition.


I think the 3rd segment which is ( 250000 and multiplier 10000 )is the only one that doesn't agree with others.



Notes:

the main problem right now is how to adjust ( 250000, multiplier 10000).

take care, we have three different multipliers as follows : ( 1000, 10000, 100000 ).


if you have another idea for that, you are welcome.

thanks in advance,,,


Update_002_Sales Discount Amount_More Than Multiplier.xlsx

thanks for your help, but I prefer to use the Excel sheet as shown in post #17 for organizational purposes.

thanks for your help, please have a look at post #17, and you will get the idea and hope to find a solution for that.

The last Update for my question is as follows:-

Please have A look at Thread/post # 17


Thanks for any ideas or suggestions, I hope to find a solution.

Thanks in Advance,,,

I'm very Sorry for the unintended mistake, I just want to highlight the last thing to my question.

I repeated my deep apologies and I am sorry for any inconvenience or disturbance.

Thanks for helping me.

Thank You So Much, You are Excellent !!! , You did it.

I have a question, please let me know, how do you get it?

in the following equation:-

=IF(AND(C1<100000,MOD(C1,1000)=0),C1,IF(AND(C1<250000,MOD(C1,10000)=0),C1,IF(AND(C1<1000000,MOD(C1,10000)=0),C1,IF(AND(C1<3000000,MOD(C1,100000)=0),C1,IF(MOD(C1,10000)=0,C1,"Invalid Amount")))))


the bold part in the equation, especially ( 1000000 ), you use the multiplier 10000, NOT 100000, however, you have to use it rather than 10000, according to the sales segment.


you change the use of ( multiplier ) and are not restricted to the rules of the discount rate, however, the equation has been met.

Waiting your reply, thank you so much again,,,

I mean by that:- if you go back to thread #17, please have a look at to Excel sheet, and compare it with the Excel sheet you mentioned in thread #24, you will see the difference.

the main difference is that: you change ( multiplier 10000) and replace it with another one and I didn't know why you do that.

Sorry for being late to reply and Thanks again for helping me.

I don't understand your answer, I understand that you replaced the multiplier 10000 with 100000.

could you please use the following multipliers:-
250000, multiplier 10000
1000000, multiplier 100000 NOT 10000?



Does it make a difference if we use the multiplier 10000 with 1000000 ?

I mean by that, is it correct to say:- ( 1000000, multiplier 10000) = ( 1000000, multiplier 10000) ?


Thanks in advance,,,

First of all, Happy New Year, and hope all of you are well, thanks for your efforts to help me.

I have been trying your solution more than ten times to find what I'm asking for it, unfortunately, no answer find answer what I'm looking for.

all I need just the solution to my question as mentioned in Thread #25.


Notes:-
we still have the same problem, I changed the multiplier but the equation hasn't been met, please go back to thread to thread #25.


Sorry for any inconvenience & disturbance, and thanks in advance,